conditional formattingmicrosoft excelworksheet-function
Can someone please tell me how to highlight multiple cells in a row based on some values in any one of a given number of cells?
For example, if in Row 2 any of the cells from D2 to L2 have value "A" or "B" or "C" then I want Row 2 font to be of color Blue.
I am using Microsoft Excel 2008 for Mac.
You can mimic this in Excel 2007/2010 by using a formatting rule over the range in question:
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.
Best Answer
You can count the number of occurrences of "A", "B" and "C" in each row using
COUNTIF, and then add the results together. If any of those values are present in a row, the sum should return a non-zero value.Try this:
Go to Conditional Formating > New Rule > Use a formula to determine which cells to format
Enter this formula:
Set your format and click OK.
Make sure that the Applies to box in the Conditional Formatting Manager window is set to
=$D2:$L2, or the row(s)/range(s) that you wish to apply the conditions to.These should also work: