Active Cell Highlighting in VBA

[JBShandrew]

Can some one please correct my incorrect syntax? I would like the cell that I am currently in to change the background color. This is what I have

.Range("C6:C15").ActiveCell.Interior.Color = RGB(255, 255, 0)

There are 9 cells, when I click in a cell or arrow down, or press the enter key to advance to the next cell in the range I would like it to be highlighted.

Thank you in advance,

Sincerely,

J.B.

 

[Trebor76]

Hi there,

You need to lose the ActiveCell reference, i.e. try this to make the nine cells in C6:C15 yellow:

ActiveSheet.Range("C6:C15").Interior.Color = RGB(255, 255, 0)

HTH

Robert

 

[JBShandrew]

Hi there,

You need to lose the ActiveCell reference, i.e. try this to make the nine cells in C6:C15 yellow:

ActiveSheet.Range("C6:C15").Interior.Color = RGB(255, 255, 0)

HTH

Robert

Thank you for the reply.

Your code works very good. However, I want the cell to highlight when I am in the cell. I have seen this on youtube, but they did not explain how they made the cell fill and unfill.

 

[Trebor76]

Interesting concept!!

May impact on performance, but try this event macro* on the tab in question:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Select Case Target.Address
        Case "$C$6", "$C$7", "$C$8", "$C$9", "$C$10", "$C$11", "$C$12", "$C$13", "$C$14", "$C$15"
            For Each rngcell In Range("C6:C15")
                If rngcell.Address = Target.Address Then
                    rngcell.Interior.Color = RGB(255, 255, 0)
                Else
                    rngcell.Interior.Color = xlNone
                End If
            Next rngcell
        Case Else
            Range("C6:C15").Interior.Color = xlNone
    End Select

End Sub

* To install this macro follow these five steps

1. Copy the code to the clipboard
2. Right click the tab you wish the code to run
3. From the shortcut menu select View Code
4. Paste the code from step 1 onto the blank module
5. From the File menu select Close and Return to Microsoft Excel

HTH

Robert

 

[JBShandrew]

Interesting concept!!

May impact on performance, but try this event macro* on the tab in question:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
    Select Case Target.Address
        Case "$C$6", "$C$7", "$C$8", "$C$9", "$C$10", "$C$11", "$C$12", "$C$13", "$C$14", "$C$15"
            For Each rngcell In Range("C6:C15")
                If rngcell.Address = Target.Address Then
                    rngcell.Interior.Color = RGB(255, 255, 0)
                Else
                    rngcell.Interior.Color = xlNone
                End If
            Next rngcell
        Case Else
            Range("C6:C15").Interior.Color = xlNone
    End Select
 
End Sub

* To install this macro follow these five steps

1. Copy the code to the clipboard
2. Right click the tab you wish the code to run
3. From the shortcut menu select View Code
4. Paste the code from step 1 onto the blank module
5. From the File menu select Close and Return to Microsoft Excel

HTH

Robert

Thank you Robert

There was just one little error, that I was able to correct by using Dim to define rngcell. 1st time I have used Dim. I'm surprised that it worked for me. Now I need to study the code that you wrote so that I can have an understanding of how to use it again.

I used this on the sheet, because it did not work with the other macros, so I put it with the only place that did not have a macro written.

Sincerely,

J.B.



Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'With Sheets("Sheet1")Not necessary

Dim rngcell As Variant '1st time I used Dim

Select Case Target.Address
Case "$C$6", "$C$7", "$C$8", "$C$9", "$C$10", "$C$11", "$C$12", "$C$13", "$C$14", "$C$15"
For Each rngcell In Range("C6:C15")

Rem rngcell not defined

If rngcell.Address = Target.Address Then
rngcell.Interior.Color = RGB(255, 255, 0)
Else
rngcell.Interior.Color = xlNone
End If
Next rngcell
Case Else
Range("C6:C15").Interior.Color = xlNone
End Select

'End With Not necessary

End Sub

 

[Trebor76]

Hi J.B.,

Just change the 'rngCell' varaible to a range variable, not a variant variable i.e.

Dim rngCell As Range

Sorry, my bad for not defining it in the first place. Must always remember to use Option Explicit like you've done.

Regards,

Robert

 

[JBShandrew]

Hi J.B.,

Just change the 'rngCell' varaible to a range variable, not a variant variable i.e.

Dim rngCell As Range

Sorry, my bad for not defining it in the first place. Must always remember to use Option Explicit like you've done.

Regards,

Robert

Thank you again.

Now I'm courious why my Dim statement worked. I have a long way to go before I will be confident in VBA. It worked, but do not understand why, since I used the wrong syntax.

 

[Trebor76]

Now I'm courious why my Dim statement worked

Because a variant variable accounts for any kind of data except fixed-length string data and user-defined types, but it comes at a cost - it's the least efficient (in memory terms) type of variable.

 

[JBShandrew]

This is what I have now. Now I can get back to my homework for my classes. Thanks again for your help. You and the other VBA experts makes this look easy to do, but from my experience I could not do this without for myself without help from you, and the other experts here.



'With Sheets("Sheet1")Not necessary

Dim rngcell As Range '1st time I used Dim
Select Case Target.Address ' I tried to use ("$B$6:$C$6") did not work.
' is there a way to use this with the case?
Case "$B$6", "$C$6", "$B$7", "$C$7", "$B$8", "$C$8", "$B$9", "$C$9", "$B$10", "$C$10", _
"$B$11", "$C$11", "$B$12", "$C$12", "$B$13", "$C$13", "$B$14", "$C$14", "$B$15", "$C$15", _
"$B$16", "$C$16", "$B$17", "$C$17", "$B$18", "$C$18", "$B$19", "$C$19", "$B$20", "$C$20", _
"$B$21", "$C$21", "$B$22", "$C$22", "$B$23", "$C$23", "$B$24", "$C$24", "$B$25", "$C$25"

For Each rngcell In Range("B6:C25")
Rem rngcell not defined
If rngcell.Address = Target.Address Then
rngcell.Interior.Color = RGB(255, 255, 0)
Else
rngcell.Interior.Color = RGB(0, 250, 250)
End If
Next rngcell
Case Else
Range("B6:C25").Interior.Color = xlNone
End Select

'End With Not necessary
End Sub

 

[gregtx81]

Since you're making your targeted area bigger, it may pay to make your code more extensible.

Option Explicit
Private Sub Worksheet_Selectionchange(ByVal target As Range)
    
    Dim rngChangeBackground As Range
    Set rngChangeBackground = Range("B6:C25")
    
    
    If Not Intersect(target, rngChangeBackground) Is Nothing Then   'i.e. target is at least partially within rngChangeBackground
        rngChangeBackground.Interior.Color = RGB(0, 250, 250)
        Intersect(target, rngChangeBackground).Interior.Color = RGB(255, 255, 0)
    Else
        rngChangeBackground.Interior.Color = xlNone                 'target not within rngChangeBackground
    End If
End Sub