Conditional formatting based on cellpointer position?

[TomH]

I have searched and searched for this subject and can't seem to find anything on it. Is there a way to set up conditional formatting based on the value in the cell that the cellpointer is on? In the alternative, is there a formula or function that would tell me the value of of the cell where the cellpointer is positioned?

THANKS!

 

[godsspeed]

by cellpointer are you referring to the active/selected cell?

assuming the above is accurate, you want your selected cell to simply be formatted differently only while it is selected (go back to 'normal' formatting when another cell is selected) ???
I'm not versed well enough in VBA to construct the needed coding, but i would think it should be fairly 'straight foward'...

on the second part, im not sure if i understand the need for this? can you explain a little futher?

 

[TomH]

Thanks for the reply. Yes, active cell. But, what I want to do is click on a cell containing a value, and have cells in a specified range that contain that same value be formatted while that value is selected. So, for example, if I click on a cell containing the number 23, I want all cells in a specified range that also have 23 to be formatted. Then, if I click on a cell containing 61, I want all the cells that contain 61 to be formatted. I want these formats to be applied only while the matching value is selected.

 

[godsspeed]

hmmm... very interesting idea!! very applicable too!
im sure one of the coding guru's here will be able to assist. also, chandoo.org is a super helpful website (where you can post/ask a question)...many excel ninjas there!!! lol

 

[George J]

Not possible with conditional formatting, but here's a macro that i think will do what you need. Put the range you are wanting in cell A1: A2:L22 for example or amend the code for strMyRange. Right click the sheet tab and paste this in - i think this is what you were wanting.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strMyRange As String, rng As Range
strMyRange = Range("A1").Value
If strMyRange = Empty Then Exit Sub
For Each rng In Range(strMyRange)
    If rng.Value = Target.Value Then
        rng.Interior.ColorIndex = 3
    Else
        rng.Interior.ColorIndex = xlNone
    End If
Next rng
End Sub