CODEWITHSUNDEEP
vbaexcel
av abhishiek
av abhishiek

Reputation: 667

Selection of Active Cell in VBA

I am new to VBA and was learning it via doing exercises, one of the first macros was to color a box red If I click on a macro button. I recorded a macro initially to check whats the VBA code it uses to do that

Sub MakeMeRed()
'
' MakeMeRed Macro
'
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 192
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

I googled around found that there is another method to select active cell, so I was trying that method to fill the cell.

Sub TestMacro()
'
' TestMacro Macro
'
    With ActiveCell
        '.Value = "250"
        .Color = 200
    End With
End Sub

But this code does not work, it does not fill the color of selected cell. Can you point out where I'm going wrong?

Upvotes: 0

Views: 5516

Answers (2)

Vityata
Vityata

Reputation: 43575

Here are 2 ways to refer to the color, inside the cell.

Using the ActiveCell.Interior.ColorIndex and ActiveCell.Interior.Color. ActiveCell.Interior.Color can get 4 different values.

Sub TestMacro()

    With ActiveCell
        .Value = 1
        .Interior.ColorIndex = 3

        .Offset(1, 1) = 21
        .Offset(1, 1).Interior.Color = RGB(255, 0, 0)

        .Offset(1, 2) = 22
        .Offset(1, 2).Interior.Color = vbRed    'vbRed = 255

        .Offset(1, 3) = 23
        .Offset(1, 3).Interior.Color = "&HFF"   'FF = 255; &H is for typeinfo

        .Offset(1, 4) = 24
        .Offset(1, 4).Interior.Color = 255
    End With

End Sub

It looks like this:

enter image description here

Upvotes: 2

steegness
steegness

Reputation: 459

To equate the two, ActiveCell in the section is the Selection in the first. To change the color, you'd need to slide in that Interior portion somewhere. You can either have With ActiveCell.Interior or within the with block .Interior.Color = 200.

Upvotes: 0

Related Questions