Conditional formatting with Offset?

[Drrellik]

I want to highlight the cell directly above a certain value in one column.
A B
1 2
2
3
4
5
6
7
8
2
9
10
2
11
12
2
13

my value would be 2 and I want to highlight 1,8,10, & 12

hope that helps

~DR

 

[arthurbr]

Select your range
use following formula for CF
=$A2=2
or
=$A2=$B$2 (if you need to change the value)
format as required

 

[Drrellik]

Thank you but that only highlights one value. I want to locate a dynamic value in a column and highlight the cell above it.

	T	U

<tbody>
[TD="align: center"]8
[/TD]
[TD="align: right"] 11
[/TD]
[TD="align: right"]7
[/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13
[/TD]
[TD="align: right"] 6
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15
[/TD]
[TD="align: right"]24
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16
[/TD]
[TD="align: right"] 10
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18
[/TD]
[TD="align: right"]17
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22
[/TD]
[TD="align: right"] 5
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

U8 will change daily I want to locate that number in my column and highlight the number or cell above.

 

[markmzz]

Thank you but that only highlights one value. I want to locate a dynamic value in a column and highlight the cell above it.

U8 will change daily I want to locate that number in my column and highlight the number or cell above.

Hi!

Select the range T8:T23 and put the Conditional Formatting (Formula) below:

=T9=$U$8


[TABLE="class: grid, width: 246"]
<tbody>[TR]
[TD]
[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]CF- =T9=$U$8[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD][/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**[/TD]
[TD]****[/TD]
[TD]****[/TD]
[TD]****[/TD]
[TD]************[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[Drrellik]

Thank you,

That totally works but I don't understand the formula... thank you again

 

[markmzz]

Thank you,

That totally works but I don't understand the formula... thank you again

I will try to explain.

Lets go:

​

Formula =T9=$U$8

If the active cell is T8 and I tell to Excel to format the cell T8 if the cell T9 (T9 the cell below the cell T8 and T8 is the cell above T9) have the value of the cell U8 (absolute reference - fixed cell)

Then the Excel will format all cell above the cell that have the value of cell U8.

I hope that this helps.

Markmzz

 

[Drrellik]

It does, for some reason I thought it would be much harder, but CF is still a learning process.

I also moved my cell reference to the top of the same column. so =A3=$A$3 with A2 selected and then modified the range to cover A3:A200 worked

now the last random number found at the top of my list is in A3 and any other time in the is appears in the column, the random following number is highlighted

Just trying to have fun with the last 200 lottery numbers and looking for that winning number.... lol

Thanks again

 

[arthurbr]

Interesting that my solution, which is entirely the same as markmzz does not work? (BTA, I'm no MVP)

 

[markmzz]

It does, for some reason I thought it would be much harder, but CF is still a learning process.

I also moved my cell reference to the top of the same column. so =A3=$A$3 with A2 selected and then modified the range to cover A3:A200 worked

now the last random number found at the top of my list is in A3 and any other time in the is appears in the column, the random following number is highlighted

Just trying to have fun with the last 200 lottery numbers and looking for that winning number.... lol

Thanks again

Hi Drrellik,
<strike></strike>
​

You're welcome and I'm glad to help.

Markmzz



​

Interesting that my solution, which is entirely the same as markmzz does not work? (BTA, I'm no MVP)

Hi Arthurbr,

Try this:

First, select the range S8:T16 (with S8 active in the end) and then put the CF.

After that, put the value 10 in U8.


[TABLE="class: grid, width: 246"]
<tbody>[TR]
[TD][/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]CF- =$S9=$U$8[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**[/TD]
[TD]****[/TD]
[TD]****[/TD]
[TD]****[/TD]
[TD]***************[/TD]
[/TR]
</tbody>[/TABLE]


And tell me the result.

Markmzz

 

[Drrellik]

Arthurbr,

Yours does work after I changed the cell reference, the formula is basically the same I just did not understand what it was doing when I tried yours, both of you thank you for the help and now lets win the lottery.

~DR