Whether the area is populated in the rules seems to be random, but it is obviously applied correctly when the macro runs.
the attached file has two screen dumps they are the same area just scrolled up and down (it feels like a memory-disply conflict but can't be).
Assign the formula to the first data value in column B, which is B2. This formula tests the cells in column B (cells B2: B15).
If the formula for any cell in column B evaluates to True, its corresponding cell in column A (for example, A5 corresponds to B5, A11 corresponds to B11), is then formatted with a red background color.
Dear loungers, Since Excel doesn't maintain conditional formatting as I would like - once new rows and columns are added it becomes unmanageable - I have got into the habit of having a bit of VBA to reset the conditional formatting rules.
This works well in that on "pressing the button" the sheet's formatting is as I want it and the rules are set.
Fortunately, the fix didn’t take too long, but with complex formatting, things could have been much worse.We want to mark badges that expire within 60 days but are not yet expired with a yellow background color, and expired badges with a red background color.In this example, cells with employee ID numbers who have certification dates due to expire within 60 days are formatted in yellow, and ID numbers of employees with an expired certification are formatted in red. The first rule (which, if True, sets cell background color to red) tests a date value in column B against the current date (obtained by using the TODAY function in a formula).The red border really makes the list easier to read, especially if I’m looking at it before my morning coffee!The conditional formatting was set for cells A2: F9, and uses a simple formula to see if the date is equal to the date in the row above.