conditional formattingmicrosoft-excel-2010
This should be the simplest example of conditional formatting but I can't figure it out.
I have two columns seperated by several dozen other columns (C-original data, BO– calculated value) and I need to highlight the calculated value if it is not equal to the original value.
I can do this on a cell by cell basis but can't see how to apply it to a whole column without adding a rule for every row (there are many 1000s of rows).
What am I missing?
Here's a macro that creates a conditional format for each row in your selection. It does this by copying the format of the first row to EACH row in the selection (one by one, not altogether). Replace B1:P1 with the reference to the first row in your data table.
Sub NewCF()
Range("B1:P1").Copy
For Each r In Selection.Rows
r.PasteSpecial (xlPasteFormats)
Next r
Application.CutCopyMode = False
End Sub
To use, highlight the un-formatted rows in your dataset (in my case, B2:P300) and then run the macro. In the example below, note that the max numbers in the first two rows are 5 and 15, respectively; both cells are dark red.
I'm sure there's a faster solution than this, though.
To add to mischab1's answer: Using Excel 2007, getting to the formula is very simple, but also easy to miss:
1) Click "Manage Rules"
2) Click "Edit Rule"
3) In the rule, you probably have :
Format only cells with Cell Value =$A$1
Delete the $ in front of the column # - so that only the column value (A) is absolute:
Format only cells with Cell Value =$A1
4) Select OK
5) Now use format painter to apply that rule to the rest of the sheet
Best Answer
I tend to use an IF() rule for this sort of thing. The trick is then to get the '$' signs right. If column C is the original data and BO is the calculated value, then select the BO column and add a conditional formatting formula rule of:
Note there is no $ sign beside the row number, which causes it to apply to each row in turn.