How to remove conditional formatting from columns

English
,
#1

I created this spreadsheet several years ago. It has some conditional formatting applied to a few columns. It has been long enough, I can’t remember how I limited the conditional formatting to just a few of the columns.

Today, started using the next two columns. They’re using the same conditional formatting of the columns where I want it.

I’ve searched around for about half an hour and haven’t stumbled on it, yet. Could someone please tell me/remind me how to limit conditional formatting to just some columns. Another way to say it: How do I remove conditional formatting from some columns?

Thanks,
Drake Christensen

Update:

Per a suggestion, I went through these steps:

The columns I want formatted are F, G, H, I, J.
The columns I do not want formatted are K, L.

  • I went into Format-Conditional Formatting-Manage… (BTW, how did you format the menu entries here?)
  • The first entry in the list that had one of my conditions was F2136. I edited that and set the range to F2:J3309
  • I clicked OK. After several seconds, K and L were still showing conditional formatting
  • I went back into Format-Conditional Formatting-Manage…
  • I went to the end of the list and deleted all of the K and L entries
  • I clicked OK. After several seconds, the formatting disappeared from columns K and L
  • I went back into Format-Conditional Formatting-Manage…
  • As a test, I highlighted one of the listbox items that matched a spreadsheet cell visible on the screen, J3046
  • While I was in there, I noticed that all the K and L cell numbers had repopulated in the Manage listbox
  • I deleted that one cell I was intending to test, J3046
  • I clicked OK. J3046 remained formatted. But, also, the formatting had reappeared in columns K and L

Also, while I was in there, I see that the first entry for column F in the listbox that I edited to F2:J3309 still shows F2:J3309.

Further update:

I went through those steps again, and it appears to be working. A few times while trying this, the Edit sub-dialog went away, taking my edits with it. A couple of times I was trying to back out one level and it went all the back to the spreadsheet. A couple of other times, the OK button in the Edit sub-dialog bypassed the initial Manage… dialog, again, losing my edits.

#2

The ranges for CF are mostly very fragmented due to a bug. To get a specific CF applied to exactly the range you want it for, it doesn’t suffice to define the correct range in the manager. You also need to delete other CF entries including parts of ranges you don’t want to apply the CF to.

#3

Would you mind to also check if one of the conditions used in your CF is based on a formula containing a call to the STYLE() function? Please report the result.

#4

This isn’t a macro, so I’m not calling any functions. Just some Conditional Formats that refer to the style I set up for each.

#5

Who talked of a “macro”?
One of the selectable modes of CF is labelled ‘Formula is’ and requires to input a formula the result of which is interpreted as TRUE if numeric and not zero. In this case the assigned cell style gets overlaid to the original cell attributes.
The mentioned formula (condition!) may contain a call to the STYLE() function. In this case a named cell style will actually be assigned to the current cell during the evaluation of the condition.

#6

A style assigned this way will not simply vanish if the CF is deleted. That’s one of the reasons for what I called this way of using CF a “misuse”.

#7

Ah, okay, I understand. But, as my question probably demonstrates, I am not a heavy user of Calc. So, my self-made sheets are extremely simplistic. My conditionals are all “<”, “between” and “contains” type tests.

#8

When you copy a cell to another place, conditional formatting of that cell is also copied.

Use Format-Conditional Formatting-Manage...; note the ranges, and either remove formats which completely in unwanted ranges, or use Edit to modify cell range.

#9

Okay. I brought up the conditions, and see where I can limit the range. I set that to the range I want, and the two new columns, outside that range, are still being having those conditional formats applied to them.

Because they got formatted in the past, do I have to go in and delete all of the superfluous entries in the Manage list?

#10

and either remove formats which completely in unwanted ranges…

#11

I did that. They cleared for a moment. But, when I went in to test removing one of the single-cell entries that was within the range, the cells in the new columns had repopulated. When I cleared that one test cell, the formatting was visible, again.

#12

I don’t understand that. What you did, what you got. Maybe because English isn’t native for me. Provide a sample, or screenshot, or step-by-step, or better all those.

#13

There is a trick misusing the STYLE() function in conditions of CF. May this be the background of the issue? I would need to see the document to clear this, probably.

#14

Ack. It got truncated. What’s the best way to continue this?

Edited my question

#15

Still unclear. Can you provide a sample to some file sharing service, and put a link here?

#16

Select the ranges applying a conditional format (CF) against your intentions.
Go Format > Clear Direct Formatting (Ctrl+M) or simply hit Ctrl+M
Go Format > Styles > Styles and Formatting (F11) or simply hit F11

  • You should now see the Sidebar showing the title “Styles and Formatting” and a collection of cell styles.
    (The original range[s] still selected:) Select by a doubleclick the style (supposedly ‘Default’) you want to apply.
    Reassign different styles and/or direct formatting inside the range[s] concerned here as you want them to be.

(Report here again.)

#17

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.