relative highlighting

E

Ed

Hi all,

Is it possible to have a table where I select a cell to type a value in it
and by doing so, another cell is highlighted? For example when I select
"April's price" to type a value in it, it automatically highlights "March's
price" while I am in that cell. I mean to use it just as a visual aid to type
values that keeping in mind another cell's value is handy.

thankyou!
 
N

Nick Hodge

Ed

No way through formulas or the UI or anything, but you could paste some code
in the Worksheet_SelectionChange() event (right-click on a sheet tab and
select view code...)

The code below colours the cell one to the right of the selected cell in
red. If you need more specific help with the code or implimentation, post
back

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("A:A")) Is Nothing Then
Columns("B:B").Interior.ColorIndex = xlNone
Target.Offset(0, 1).Interior.Color = RGB(255, 0, 0)
End If
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]
 
E

Ed

Hello Nick, thanks, the code works great, I more or less understand part of
it, enough to choose the column that gives the highlight and the one that is
highlighted... There are somethings that I would like to know if they can be
changed. When I am in the column which gives the highlight to the other, if I
switch to a different column the last cell that was highlighted stays
highlighted. Also if I am in the column which gives the highligt and I select
a range of cells, the highlight goes as well to other cells and stays like
that. Would you know how to make the last cell highlight go away when I leave
the cell?

Does anybody know webpage where I can learn more about codes?

thanks a lot!

Ed
 
N

Nick Hodge

Ed

Does this help?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("A:A")) Is Nothing Then
Columns("B:B").Interior.ColorIndex = xlNone
Target.Offset(0, 1).Interior.Color = RGB(255, 0, 0)
Exit Sub
End If
Columns("B:B").Interior.ColorIndex = xlNone
End Sub

The addition checks to see that the selection is in column A, if it is it
highlights the cell directly to it's right (offset(0,1)), if the selection
isn't in column A then it jumps the if...then...end if statement and runs
the last columns("B:B") part to clear any colouring from column B

Hope that is what you were looking for?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]
 
Top