Forum Discussion
Conditional format - fill multiple columns base on a single column's value
I have a spreadsheet with 4 columns, and a conditional formatting rule to fill cells in column B to pink if the value is > 0. How can I also make the cells in columns A, C, and D pink based on the value in column B?
4 Replies
You may apply conditional formatting rule with formula
=$B1>0
to your entire range, let say =$A$1:$D$200
I can't figure out how to edit this rule. Is there a way to create or access theses rules other than through the dropdown.
Also, is there anywhere that shows rule syntax?
Select your range, Conditional Formatting->New Rule->Use a formula... Here add your formula and apply desired format
To edit the rule Conditional Formatting->Manage Rules->Edit rule.
Syntax is simple - any formula which returns TRUE triggers the formatting, all other results are ignored. Bit simplified, the rule is applied to you range cell by cell starting from top left one. Relative and absolute references behaviour is to be taken into account.
Thus in our case rule in formula bar (=$B1>0) will be applied to the cell A1. For the next cell down the formula to check A2 value will be (=$B2>0), etc. Formula is modified the same way as if you type it in A1 and after that drag down and to the right, the only it's done in background.
