Conditional formatting - rules that automatically multiply
Hello,
I have an Excel table with conditional formatting where I selected a range (the entire column A) and set several rules, for example, text containing A in red, text containing B in green, and so on... However, when I copy/paste or insert a new row, it adds a new conditional formatting rule and changes the range of the existing rule. How can I avoid this? Because if every time I add a line it creates a new rule, I end up with 200 rules, and it stops working altogether...
Second question: is it possible to have some sort of legend for the conditional formatting? That is, to have reference cells and a formula that makes sure that whenever the text "A" is in a cell, it adopts the formatting of the reference cell in the legend.
I have an Excel table with conditional formatting where I selected a range (the entire column A) and set several rules, for example, text containing A in red, text containing B in green, and so on... However, when I copy/paste or insert a new row, it adds a new conditional formatting rule and changes the range of the existing rule. How can I avoid this? Because if every time I add a line it creates a new rule, I end up with 200 rules, and it stops working altogether...
Second question: is it possible to have some sort of legend for the conditional formatting? That is, to have reference cells and a formula that makes sure that whenever the text "A" is in a cell, it adopts the formatting of the reference cell in the legend.
2 answers
-
Hello
One solution: Apply conditional formatting not to the range but to the entire column (by selecting the letter of the column) the formula in the rule manager will then be =$A:$A
adding a row will not add any formula
For the legend cells, we can proceed in the same way:
on another sheet create as many columns and rules applied to the entire column as there are desired templates
To copy the formatting from a template, position yourself on the column header
take the format painter tool (the paintbrush) and return to sheet 1 and click on the header of the column where you want to apply the formatting
Best regards -
Thank you very much for your response! Sorry, I think I expressed myself poorly. Actually, I am indeed applying the conditional formatting to the entire column and I have $A:$A in the rule manager... but it's not working. In the meantime, I've noticed that in Excel 2007 on XP, there’s no problem, but with Excel 2010 it multiplies the formulas....
As for the reference cells... I didn't understand :-/-
Ok, I tested it on 2007, it works for columns but not for ranges
but I don't have 2010!
For the reference cells, I was telling you to create one for each column in another sheet and copy the formatting from those columns to apply in the first sheet, but if Excel 2010 multiplies the formulas in all cases, that won't solve your problem
We should probably drop the conditional formatting and go for a formatting by VBA macro
Here's a proposal:
In column A of Sheet2, enter all your values and color each cell; these will be the reference cells
Open the editor (ALT F11) and select Sheet 1, and in the right window, enter the following code:
Dim c(50) as integer 'Dimensional array of 50 slots for colors to adjust if necessary
Sub colorcodes()
der2 = Sheets("Sheet2").Range("A1").End(xlDown).Row 'last empty row column A Sheet 2
For n = 1 To der2
c(n) = Sheets("Sheet2").Range("A" & n).Interior.ColorIndex ' inserts the color code of each cell into an array
Next
der1 = Sheets("Sheet1").Range("A1").End(xlDown).Row ' last empty row column A Sheet1
For n = 1 To der1 'loop on Sheet 1 rows
For x = 1 To der2 ' loop on Sheet 2 rows and if values match apply the color
If Sheets("Sheet1").Range("A" & n).Value = Sheets("Sheet2").Range("A" & x).Value Then MsgBox ("OK"): Sheets("Sheet1").Range("A" & n).Interior.ColorIndex = c(x): Exit For
Next x
Next n
End Sub
Each time you call the macro, the cells in column A of Sheet 1 will be recolored based on the models from Sheet 2 (which you can change at will)
Best regards -
-
