Conditional Formatting Question

[Thresh1642]

I have a fairly basic Spreadsheet.
What I am trying to do is apply a conditional format to it where the formatting might change based on an input.

So in this case the range would be C5 to E7, but the value in C3 will determine the formatting in C column, the value in D3 will determine the value in the d column and so on.

I know I can go thru and set up a rule for each column, is there a formula I can apply to the range instead?

Appreciate the help!

 

[6StringJazzer]

How does the value in C3 determine the formatting in C column? What value causes what formatting? Same question for all the columns you want to format.

Let's say that for all those columns, you want to fill cells with green if the value in row 3 of that column is >5.

Rule using a formula:

=C$3>5

Applies To range:

$C:$E

 

[Thresh1642]

Hi Jeff,

Thanks for the reply.

What I am looking for similar to the answer you provided, however the formatting would apply to just that column and not the range, so C3 will affect C5:C99, D3 will affect D5:D99 ands so on, and anything > than the number in cell three would be one color/format, and < would be another color/format. Essentially the number in cell 3 will determine the formatting of the rest of the column.

I am looking at dataset that has well over 30 columns, and I am wondering if there is a way to set up a formula in the conditional formatting to apply to the tab as a whole, instead of setting up 60+ conditional formulas?

 

[6StringJazzer]

Yes, you can apply the rule I gave to all columns, because the column is a relative reference (the row is an absolute reference because of the $).

You just need two rules.

Applies To $C:$AZ (where AZ is whatever your last column is. I have used the entire columns instead of worrying about what is the last row. That gives you more flexibility.)

=AND(ROW(C1)>=5,C1>C$3)

Applies To $C:$AZ

=AND(ROW(C1)>=5,C1<=C$3)

You said > and < but did not mention =.

 

[Thresh1642]

Hi Jeff,

This is what I was looking for, I appreciate the help!

 

[6StringJazzer]

Glad it worked!