CODEWITHSUNDEEP
excelvba
BoDoesn'tKnowsVBA
BoDoesn'tKnowsVBA

Reputation: 3

VBA code to find a color in a selection and change it

Trying to write a code that will find red cells and change them to green within a selection. I am a total beginner so this will look barbaric but here it is. I can get it to go down a column and change the colors along the way but what I am really looking to do is have it do that within a selection and when it gets to the bottom of the selected column go back up to the next column within the selection and so on until there is no data available. Here is what I have. Thanks a bunch for any help!

Sub change_colour()



Do While ActiveCell.Value <> ""

Do While Selection.Interior.Color = 255
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0

    End With

    ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

Upvotes: 0

Views: 868

Answers (1)

Soulfire
Soulfire

Reputation: 4296

What I would do is set a Range variable to the current selection, and just loop through that range and check the color. If it is red, change it.

You're close, but there's some unnecessary code here (which is to be expected as you're learning).

I think the key is declaring and assigning the Range variable and the use of a For..Each loop.

Public Sub Change_Colour()

    Dim rSel As Range, c As Range

    Set rSel = Selection

    For Each c In rSel

        If c.Interior.Color = 255 Then

            c.Interior.Color = 5296274

        End If

    Next c

End Sub

So this code declares two range variables rSel and c. rSel is initiated as the user's selection. I use the For..Each loop with the range variable c to loop through all of the cells in the selection.

The If statement is a stripped-down version of what you have, checking to see if the cell's color is red and changing it to green if so.

Upvotes: 1

Related Questions