CODEWITHSUNDEEP
excelvba
SONIA
SONIA

Reputation: 1

Color cells with specific data

I have a macro to color cells that have the word VOID in it.

I also have the word VOID in a cell like this: [$189.00VOID].

I can't find a way to color all cells that contain:

VOID and [$189.00VOID]

or any other dollar amount in it.

Sub Macro1()
On Error Resume Next
Dim current As String

For i = 1 To 65536 ' go from first cell to last

    current = "c" & i ' cell counter

    Range(current).Select ' visit the current cell

    If Range(current).Text = "VOID" Then ' if it says VOID then we...
        With Selection.Interior
            .ColorIndex = 3 ' ...go red
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
    End If

    If Range(current).Text = "FORWARDED" Then ' if it says FORWARDED then we...
        With Selection.Interior
            .ColorIndex = 4 ' ...go green
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
    End If
Next i ' loop and check the next cell
End Sub

Upvotes: 0

Views: 6492

Answers (2)

tigeravatar
tigeravatar

Reputation: 26670

For something like this, I would really recommend using conditional formatting (as has already been stated). Here are the two Condtional Format formulas that you would need to apply to column C:

=COUNTIF($C1,"*VOID*")>0
=COUNTIF($C1,"*FORWARDED*")>0

However, if it absolutely has to be VBA, then right-click the sheet tab that you want to monitor and select "View Code". In there, paste the following:

Private Sub Worksheet_Calculate()

    Dim rngColor As Range
    Dim rngFound As Range
    Dim strFirst As String
    Dim varFind As Variant

    'Remove current formatting (if any)
    Columns("C").Interior.Color = xlNone

    'Check for both VOID and FORWARDED
    For Each varFind In Array("VOID", "FORWARDED")

        'Attempt to find a cell that contains varFind
        Set rngFound = Columns("C").Find(varFind, Me.Cells(Me.Rows.Count, "C"), xlValues, xlPart)

        'Check if any cells were found
        If Not rngFound Is Nothing Then

            'The first cell was found, record its address and start rngColor
            strFirst = rngFound.Address
            Set rngColor = rngFound

            'Begin loop
            Do

                'Add found cell to rngColor
                Set rngColor = Union(rngColor, rngFound)

                'Advance loop by finding the next cell
                Set rngFound = Columns("C").Find(varFind, rngFound, xlValues, xlPart)

            'Exit loop when back to first cell
            Loop While rngFound.Address <> strFirst

            'Fill rngColor based on varFind
            Select Case varFind
                Case "VOID":        rngColor.Interior.Color = vbRed
                Case "FORWARDED":   rngColor.Interior.Color = vbGreen
            End Select

        End If
    Next varFind

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheet_Calculate
End Sub

Upvotes: 1

Arconath
Arconath

Reputation: 453

VBA really seems like overkill for this. As pnuts said, conditional formatting will do everything you need.

Select the cell(s) you want to format, then Home Ribbon -> Conditional Formatting -> New Rule -> Format Only Cells that Contain

Then change the first combo box from Cell Value to specific text. and in the empty text box on the right type VOID.

You can then adjust the cell formatting to be whatever you want.

Upvotes: 1

Related Questions