Changing font properties of multiple cells based on one cell's value

0
votes

I'm putting to good use one of the Excel posts on Conditional Formatting, but I'm having trouble applying it to multiple cells. I have a row of cells:

F4;G4;H4;I4;J4

in which G4 is a Yes/No dropdown list (these values come from another sheet). I tried:

Conditional Formatting > New Rule > Use a formula to determine which cells to format

in the Format values where this formula is true I inserted =(G4="No") and it would apply bold + gray + strikethrough to the row of cells mentioned above (including G4).

Problem: when I change the G4 value to No, only the first cell - F4 - changes.

What am I doing wrong?

1
excelexcel-formulaconditional-formatting
=$G4="No" should work. Just like with formulas in a cell. - hsan
That's the glitch. Thanx! - Pedro
@pnuts Thanks, but it's fine. Your answer is good and I'm not here for the rep :) - hsan

1 Answers

1
votes

As mentioned by @hsan:

=$G4="No" should work

but also the "Applies to" range should be: 

=$F$4:$J$4

Without the anchor ($) the references are relative, so F triggers the formatting because, relative to F, G is one column to the right and from F one column to the right shows No. It may be easier to see what is happening if, with the “wrong” formula (but the same Applies to range of F:J) you change the content of J4 to No. I4 should now be formatted bold+grey+strikethrough.

Maybe think of the CF as rastering through the specified range, so where that is =$F$4:$J$4, it starts in F4 and considers “does G4 equal No?” then steps to G4 and asks “does H4 equal No?” – and so on.

With the anchor, the comparison is against ColumnG at every step in the cycle.