Shading a cell range based on where the user is typing

[andrewb90]

Can I have a range of cells (B9:G9) highlight if any cell within that range is being clicked on or typed into? I would want to apply this to rows 9:67.

Thanks,
Andrew

 

[JoeMo]

Can I have a range of cells (B9:G9) highlight if any cell within that range is being clicked on or typed into? I would want to apply this to rows 9:67.

Thanks,
Andrew

Try this: Right-click on the worksheet tab and select 'View Code'. In the VBE window that opens, paste the code below. Then close the VBE window and save the workbook.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Range("B9:G67")
If Not Intersect(Target, rng) Is Nothing Then
    rng.Interior.ColorIndex = 6
End If

End Sub

You can change the color index number in the code to get the fill color you want.

 

[andrewb90]

Ok, that is close, but when I select a cell I only want those cells in that row within the range to be highlighted and when I click a different cell the highlighted cell should no longer be highlighted, and if necessary new cells would be highlighted if within the parameters.

Sorry to make things so tricky, I am not very good with this aspect of excel.

Many Thanks,

Andrew

 

[JoeMo]

Ok, that is close, but when I select a cell I only want those cells in that row within the range to be highlighted and when I click a different cell the highlighted cell should no longer be highlighted, and if necessary new cells would be highlighted if within the parameters.

Sorry to make things so tricky, I am not very good with this aspect of excel.

Many Thanks,

Andrew

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Range("B9:G67")
rng.Interior.ColorIndex = xlNone
If Not Intersect(Target, rng) Is Nothing Then
    Range("B" & Target.Row, "G" & Target.Row).Interior.ColorIndex = 6
End If
End Sub

 

[andrewb90]

So this one didn't seem to do anything at all. And is there any way to put in color codes differently as well?