relative vs absolute in conditional formatting

[lezawang]

Hi
I am try to understand conditional formatting. I could not understand why the author used this formula

=$B2>75

why $B2

She explained here (below) but I did not understand. I would appreciate if you can explained easier for me. Thanks a lot.

+++++++++++++++++++++++++++++++++++++
we'll refer to cell B2, because it's in the active row.
=$B2>75
We use an absolute reference to column B ($B), to ensure that the conditional formatting in all columns refers to the value in column B.
If we used a relative reference (B), the formula will be adjusted in each column, and won't work properly. Each cell would refer to the cell to its right, instead of refering to the cell in column B.

 

[dreid1011]

Maybe some pictures

This first table is how CF applies the formula: =$B2>75

[table="width: 400, class: Grid"]
[tr]
[td][/td]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[td]E[/td]
[/tr]
[tr]
[td]1[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]2[/td]
[td]cf: =$b2>75[/td]
[td]76[/td]
[td]cf: =$b2>75[/td]
[td]cf: =$b2>75[/td]
[td]cf: =$b2>75[/td]
[/tr]
[tr]
[td]3[/td]
[td]cf: =$b3>75[/td]
[td]65[/td]
[td]cf: =$b3>75[/td]
[td]cf: =$b3>75[/td]
[td]cf: =$b3>75[/td]
[/tr]
[tr]
[td]4[/td]
[td]cf: =$b4>75[/td]
[td]53[/td]
[td]cf: =$b4>75[/td]
[td]cf: =$b4>75[/td]
[td]cf: =$b4>75[/td]
[/tr]
[tr]
[td]5[/td]
[td]cf: =$b5>75[/td]
[td]100[/td]
[td]cf: =$b5>75[/td]
[td]cf: =$b5>75[/td]
[td]cf: =$b5>75[/td]
[/tr]
[/table]

This second table shows which cells from the above formula would be highlighted. (Assume column B also has the cf applied to it)

[table="width: 400, class: Grid"]
[tr]
[td][/td]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[td]E[/td]
[/tr]
[tr]
[td]1[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]2[/td]
[td]true[/td]
[td]76[/td]
[td]true[/td]
[td]true[/td]
[td]true[/td]
[/tr]
[tr]
[td]3[/td]
[td]false[/td]
[td]65[/td]
[td]false[/td]
[td]false[/td]
[td]false[/td]
[/tr]
[tr]
[td]4[/td]
[td]false[/td]
[td]53[/td]
[td]false[/td]
[td]false[/td]
[td]false[/td]
[/tr]
[tr]
[td]5[/td]
[td]true[/td]
[td]100[/td]
[td]true[/td]
[td]true[/td]
[td]true[/td]
[/tr]
[/table]

This third table shows how CF applies the formula: =B2>75

[table="width: 400, class: Grid"]
[tr]
[td][/td]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[td]E[/td]
[/tr]
[tr]
[td]1[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]2[/td]
[td]cf: =b2>75[/td]
[td]76[/td]
[td]cf: =d2>75[/td]
[td]cf: =e2>75[/td]
[td]cf: =f2>75[/td]
[/tr]
[tr]
[td]3[/td]
[td]cf: =b3>75[/td]
[td]65[/td]
[td]cf: =d3>75[/td]
[td]cf: =e3>75[/td]
[td]cf: =f3>75[/td]
[/tr]
[tr]
[td]4[/td]
[td]cf: =b4>75[/td]
[td]53[/td]
[td]cf: =d4>75[/td]
[td]cf: =e4>75[/td]
[td]cf: =f4>75[/td]
[/tr]
[tr]
[td]5[/td]
[td]cf: =b5>75[/td]
[td]100[/td]
[td]cf: =d5>75[/td]
[td]cf: =e5>75[/td]
[td]cf: =f5>75[/td]
[/tr]
[/table]

Does this help at all?

 

[lezawang]

Thanks a lot again. I start getting the idea of selecting .. now row 2 (because B2 is there) is going to be highlighted. that is fine but at same argument, i can say why not the whole column B got highlighted? it was selected too? Thank you.

 

[Scott T]

Think of it as you entering the formula and copying it in the spreadsheet(this is what conditional formatting is doing behind the scene)

NAME Address DATA
Bob 123 fs data beth FALSE FALSE FALSE
Beth 5t25 data1 TRUE TRUE TRUE
Rick sg stuff FALSE FALSE FALSE
Coral fs things FALSE FALSE FALSE

If you do not get the right true and false when you do this you will not get the right conditional formatting.

 

[dreid1011]

Thanks a lot again. I start getting the idea of selecting .. now row 2 (because B2 is there) is going to be highlighted. that is fine but at same argument, i can say why not the whole column B got highlighted? it was selected too? Thank you.

If you put the formula as =$B$2, then the whole column would highlight if B2 was greater than 75. With only $B2, then the cf adjusts the formula just like it adjusts when you copy it down a column. It goes $B2, then $B3, then $B4 etc ... each row compares against its respective cell in column B. ie: Row 75 would check cell B75, not B2.

 

[Joe4]

I start getting the idea of selecting .. now row 2 (because B2 is there) is going to be highlighted. that is fine but at same argument, i can say why not the whole column B got highlighted? it was selected too?

I cannot tell if you are saying you are good now, or are still failing to understand.

If you still do not understand, it seems that you do not understand what the "selected range" is. It is whatever range you highlight with your mouse, or select with your cursor. It could be one single cell, but it doesn't have to be. It can be whole rows, whole columns, or selected multi-cell ranges.

Here is a simple macro that will tell you what range you have selected at that point in time:

Sub MySelection()
    MsgBox Selection.Address(0, 0) & " has been selected."
End Sub

"B2" represents one single cell, specificially cell B2
"A10:Z50" represents the range from cell A10 to cell Z50
"2:7" represents rows 2 through 7
"D:F" represents columns D through F
etc

 

[lezawang]

Yes got it. I am so grateful to all of you indeed. Many thanks and great respect for your amazing skills and knowledge.
So grateful once again. Thank you all.