MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Please Help !!! How do you make a cell change the background color when clicked on

Posted by MARK F on March 14, 2001 4:40 PM

Hello I have a large pricing program that will highlight specific cells when they do not contain any data. My question is, How could you get the cell to change to a specific color by clicking only on it. this needs to work for many cells individually and the size of the code doesnt matter.

Thanks for any help

Posted by Dave Hawley on March 15, 2001 12:14 AM

Hi Mark

Right click on your sheet name tab and select "View Code", paste in this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim WatchRange As Range
If Target.Cells.Count > 1 Then Exit Sub
Set WatchRange = Range("A1:A10")
If Not Intersect(Target, WatchRange) Is Nothing Then
Range("A1:A10").Interior.ColorIndex = xlNone
Target.Interior.ColorIndex = 45
End If
End Sub

Push Alt+Q and save.

Each time you select a cell within the range A1:A10 the cell will become orange and all others will have no color.


OzGrid Business Applications

Posted by Mark F on March 15, 2001 1:28 PM

Re: thanks dave

Hey thanx for taking the time to help me out and by the way I work with the Steve that is always posting here and he is the one who told me about the site