# Conditional formatting based on a formula on another sheet

1. ## Conditional formatting based on a formula on another sheet

Greetings..... I would like to format a three columns depending on the result of a formula on another sheet within the same workbook.

Column A | Column B | Column C The result of the formula is one of 3 results (1 or 2 or 3)
If result is 1 - Green | Grey | Grey
If result is 2 - Gray | Green | Grey
If result is 3 - Grey | Grey | Green

Thanks

Steve

2. ## Re: Conditional formatting based on a formula on another sheet

Ok Steve.

Break this down.
A: Conditional formatting overrides your fixed format
B: 6 of your 9 Cells are Grey to Set the fixed format for Columns A to C to Grey

Set Conditional formatting for the Range =\$A:\$A using the formula =Sheet1!\$A\$1=1 to Set the fill colour to Green

Set Conditional formatting for the Range =\$B:\$B using the formula =Sheet1!\$A\$1=2 to Set the fill colour to Green

Set Conditional formatting for the Range =\$C:\$C using the formula =Sheet1!\$A\$1=3 to Set the fill colour to Green

3. ## Re: Conditional formatting based on a formula on another sheet

So if the result is Sheet"References"(\$I\$5)formula produces the result of 1, I would like Column A fill Green, and if the same cell has a result of 2 or 3, I would like it to fill Grey.

If the result is Sheet"References"(\$I\$5)formula produces the result of 2, I would like Column B fill Green, and if the same cell has a result of 1 or 3, I would like it to fill Grey.

and if the result is Sheet"References"(\$I\$5)formula produces the result of 3, I would like Column C fill Green, and if the same cell has a result of 1 or 2, I would like it to fill Grey.

I would think this wouldn't be so difficult, but when I try to write the condition for even just the cell, it will not work

eg: ="References"\$I\$5=1 - Fill Green (and it does nothing!)

4. ## Re: Conditional formatting based on a formula on another sheet

Assuming the entries in References!I5 are limited to 1, 2 or 3 then:
1. Formula to turn column A green: =References!\$I\$5=1
2. Formula to turn column A grey: =References!\$I\$5<>1
Similar formulas are used for columns B:C
Let us know if you have any questions.

There are currently 1 users browsing this thread. (0 members and 1 guests)