=indirect("g"&row())=3

[hro5e]

I have this formula for conditional formatting:

=INDIRECT("G"&ROW())=3

This works perfectly fine, now i want change colour of cells if 2 different rows contain a certain number, so:

Column G = 3
and
Column K = 2 or 3

and the rows should be highlighted only if both columns match?

Help?

 

[T. Valko]

I have this formula for conditional formatting:

=INDIRECT("G"&ROW())=3

This works perfectly fine, now i want change colour of cells if 2 different rows contain a certain number, so:

Column G = 3
and
Column K = 2 or 3

and the rows should be highlighted only if both columns match?

Help?

Curious as to why you're using:

=INDIRECT("G"&ROW())=3

Why not just use the actual cell reference? Something like:

=G2=3

Once we understand that then we can figure out the rest of your request.

 

[hro5e]

Curious as to why you're using:

=INDIRECT("G"&ROW())=3

Why not just use the actual cell reference? Something like:

=G2=3

Once we understand that then we can figure out the rest of your request.

Hi, i am using this formula because the range is all of column G and the list in column G keeps growing as new rows of data are inputted. Hope this makes sense.

i am not just trying to highlight one cell, it is the entire column if the results match.

 

[Jonmo1]

That still doesn't really explain the need for Indirect...

Try

=AND($G2=2,OR($K2=2,$K2=3))

 

[Scott Huish]

Explaining that further:

If you have relative references in your conditional formatting formulas, they will adjust for the rows just like they do if a formula is copied down.

 

[hro5e]

That still doesn't really explain the need for Indirect...

Try

=AND($G2=2,OR($K2=2,$K2=3))

Ok yes formula works but only on one cell, how do i apply to the entire column?

 

[Scott Huish]

If you are selecting all of column M prior to conditonal formatting change the 2 in the cell references to 1

=AND($G1=2,OR($K1=2,$K1=3))

 

[Jonmo1]

Ok, it's all in the method used to actually enter the conditional formatting formula.

Select Cell M1
Conditional formatting formula is
=AND($G1=2,OR($K1=2,$K1=3))
Apply the color blue for that condition.

Now COPY M1
Select the rest of the column M
Right Click - Paste Special - Formats


Now, I highlighted the 1's red.
If the first cell you put the conditional formatting in is M1, leave the formula as is.
But if you first put it in M2 instead, then change the 1's to 2's.
In other words, adjust the row #s in the formula to reflect the row you enter the formula in.
When you copy / paste special the formats down the column, the Row#s will adjust automatically.

 

[T. Valko]

Ok i will try to explain better: the background colour is Blue.

the range selected for conditional formatting is M:M (meaning entire column M) and if any cell in column G has the number 2 and the cell on the same row in column K has the number 2 or 3 then the cells are to turn Green.

Now for example row 12, the cell G12 has the number 1 and the cell in K12 has the number 2 then the background colour will stay Blue.

now if row 13, the cell G13 has the number 2 and the cell in K13 has the number 2 then the background will change to green.

Does that make sense?

the formulas you provided does not work.

What version of Excel are you using?

You want the ENTIRE column M to be formatted? That could mean up to 1,048,576 cells if you're using Excel 2007 or later.

 

[hro5e]

Ok, it's all in the method used to actually enter the conditional formatting formula.

Select Cell M1
Conditional formatting formula is
=AND($G1=2,OR($K1=2,$K1=3))
Apply the color blue for that condition.

Now COPY M1
Select the rest of the column M
Right Click - Paste Special - Formats


Now, I highlighted the 1's red.
If the first cell you put the conditional formatting in is M1, leave the formula as is.
But if you first put it in M2 instead, then change the 1's to 2's.
In other words, adjust the row #s in the formula to reflect the row you enter the formula in.
When you copy / paste special the formats down the column, the Row#s will adjust automatically.

Perfect, sorry for the confusion, yes using paste special to format works, i have been working on this spreadsheet for hours and my head is about to explode, thank you guys, works perfect.