Solved Highlighting cellif different cell is selected

March 28, 2013 at 09:24:47
Specs: Windows XP
I am trying to change the interior color of cell B15 to RGB(255,255,51) if any cell in the range C15:I15 is selected. I have the code in the sheet that I am working on and my code is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$C$15:$I$15" Then
Range("B15").Interior.Color = RGB(255, 255, 51)
End If

End Sub


Can anyone see where my mistake is? When I choose a cell in the range C15:I15, nothing happens. I also have another scenario where the selected cell is not a range of cells but a single cell. Ex. If Cell J20 is selected, change interior color of B20. Will this be different or will I just need to change the range?

Thanks for your help!


See More: Highlighting cellif different cell is selected

Report •

#1
March 28, 2013 at 12:00:46
✔ Best Answer

First a posting tip:

Please use the pre tags when posting code in this forum. Click on the blue line at the end of this post for instructions on using the pre tags for posting data and code.

As for your question...

If you were to highlight C15:I15, you would see that B15 would turn Yellow.

That's because the address of single cell would be e.g. $C$15, but you have told VBA to check for the address of an entire Range.

What you really want to do is have VBA determine whether or not the Selection is within the stated Range. In other words, does the Selection Intersect the Range(C15:I15).

This is one of those cases where "reverse logic" works the best. If 2 ranges do not Intersect, the Intersect method returns "Nothing". If they do Intersect, then the Intersect method returns all of the properties related to the Intersection.

Since you want to know if the Selection and the Range Intersect, you do not want the Intersect method to return Nothing, so you can check to see if it is "Not Nothing" as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 If Not Intersect(Target, Range("$C$15:$I$15")) Is Nothing Then
  Range("B15").Interior.Color = RGB(255, 255, 51)
 End If

End Sub

If you understand that part, I think you should be able to answer your second question on your own. If not, come on back.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 28, 2013 at 14:15:07
Thank you very much for your help. I will be sure to follow the posting rules if I have to post again.

I was able to get the code in and everything is working now. I added the opposite of this code preceding this code so that the cell will then unhighlight when a cell not in the range was chosen.


Report •
Related Solutions


Ask Question