Conditional Formatting based on FORMAT of another cell

[Uhrlaff]

I have a spreadsheet where columns E - W each has some conditional formatting to shade the cell in blue color for various scenarios. I would like to have one more Conditional format, specifically in Column A, where that cell lights up in a certain color (say orange) any time AT LEAST ONE of the other cells in that row has the blue shading. this way the reviewer can easily see the flagged orange color in column A without having to scroll left and right, back and forth at the same time as moving downward in the spreadsheet. if you can help me define the conditional format within the Column A, I'll appreciate it.

 

[JoeMo]

The CF for col A should use the existing CF formula(s) for cols E:W. CF conditions must be based on what cells are returning not their format. Can you post your CF formulas for columns E:W?

 

[Uhrlaff]

In Column E, here is my formula (and the other 18 columns have a very similar formula): =OR(AND(COUNTIF(C1, "*File 1*"), COUNTIF(C2, "*File 2*"), OR(A1 = "", A1 = "Multiple"), A2 = "Existing Pt", E2 <> E1), AND(COUNTIF(A2, "*Line Below*") > 0, E2 <> E3))

I can't imagine pasting this formula 19x into the Column A cell....

Dave

 

[JoeMo]

In Column E, here is my formula (and the other 18 columns have a very similar formula): =OR(AND(COUNTIF(C1, "*File 1*"), COUNTIF(C2, "*File 2*"), OR(A1 = "", A1 = "Multiple"), A2 = "Existing Pt", E2 <> E1), AND(COUNTIF(A2, "*Line Below*") > 0, E2 <> E3))

I can't imagine pasting this formula 19x into the Column A cell....

Dave

Me either. Maybe someone else can suggest a simpler solution.

 

[Uhrlaff]

Perhaps a good solution is as follows: I could choose another 19 columns further to the right, for example AA - AU, and assign the exact same conditional formatting, but instead of shading in blue color, I can assign a number such as 22. and then in column A I can put in my CF formula that refers to those 19 columns looking for "22" ... sort of like =OR(AA2 = "22", AB = "22", AC = "22", etc.

Dave

 

[Joe4]

Perhaps a good solution is as follows: I could choose another 19 columns further to the right, for example AA - AU, and assign the exact same conditional formatting, but instead of shading in blue color, I can assign a number such as 22. and then in column A I can put in my CF formula that refers to those 19 columns looking for "22" ... sort of like =OR(AA2 = "22", AB = "22", AC = "22", etc.

Or even easier yet, have each of the 19 formulas return a 1 if the condition is met, and a 0 if it is not.
Then all you need to do for the conditional formatting is sum those 19 columns and see if it is greater than 0 (if it is, than at least one condition is met).

 

[Uhrlaff]

Yes, that sounds real good to me. I will work on this and try to let you know the outcome. Thank you very much.
Dave

 

[Joe4]

You are welcome.
Glad we were able to help.