Home

Conditional format - fill multiple columns base on a single column's value

BobFiske
New Contributor

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

image.png

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.

Thank you so much!  Works like a charm.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
IIS extension is not working - WAC 1909
HotCakeX in Windows Admin Center on
11 Replies