Conditional Format

Question

Need help with conditional format, How do you managed rules for when top roll is set for closed, pending or open to change column to same format as if its closed, open or pending? When click on drop box on W1 and pick closed (which is gray box with red letter) I need column W and Z to change to gray and red letter when its closed. Same with Open ( green box with green Letter) and Pending is yellow with blue letters.

Answer 1

Select all the columns that you want to format this way.

For the sake of illustration, I will assume that the selection begins in column W, and that the active cell in the selection is in column W.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=OFFSET(W$1,0,-ISEVEN(COLUMN(W$1)))="CLOSED"

Click Format...

Activate the Font tab.

Select red as font color.

Activate the Fill tab.

Select gray as fill color.

Click OK. then click OK again.

Repeat these steps, but with the formula

=OFFSET(W$1,0,-ISEVEN(COLUMN(W$1)))="OPEN"

and with dark green as font color and light green as fill color.

Also repeat them with the formula

=OFFSET(W$1,0,-ISEVEN(COLUMN(W$1)))="PENDING"

and with blue as font color and yellow as fill color.