CODEWITHSUNDEEP
vbaexcelif-statement
Fiona
Fiona

Reputation: 477

Conditional Formatting Numbers in brackets in VBA

Below shows an example of the data set I hold in Excel and would like to write an if statement which compares Col1 to Col2. 

Col1 Col2
(2)  (6)
(10) (7)

If Col1 is greater than Col2 I want to highlight Col1 red and if Col1 is less than Col2 I want to highlight green.

I have tried the following if statement already in VBA.

For lngI = 1 To 2
If Worksheets("Front End").Cells(lngI + 11, 4).Value > Worksheets("Front End").Cells(lngI + 11, 6).Value Then
    Worksheets("Front End").Cells(lngI + 11, 4).Interior.ColorIndex = 3
    Worksheets("Front End").Cells(lngI + 11, 4).Font.Color = 2
ElseIf Worksheets("Front End").Cells(lngI + 11, 4).Value < Worksheets("Front End").Cells(lngI + 11, 6).Value Then
    Worksheets("Front End").Cells(lngI + 11, 4).Interior.ColorIndex = 50
    Worksheets("Front End").Cells(lngI + 11, 4).Font.Color = 2
End If
Next lngI

It is not very robust as it does not always work as there are brackets involved. As you can see, the code is needed in VBA. The numbers shown in the code are an example of where some cells are in my current code and I don't mind if these are changed to aid in any explanation.

Any help would be great. Thanks.

Upvotes: 1

Views: 277

Answers (1)

Vityata
Vityata

Reputation: 43585

If you simplify it a bit, something like this may be reached:

enter image description here

using the following:

Option Explicit

Public Sub TestMe()

    Dim lngI    As Long
    Dim lngA    As Long
    Dim lngB    As Long

    For lngI = 1 To 3
        With Worksheets(1)
            lngA = Replace(Replace(.Cells(lngI, 1), "(", ""), ")", "")
            lngB = Replace(Replace(.Cells(lngI, 2), "(", ""), ")", "")

            If lngA > lngB Then
                .Cells(lngI, 1).Interior.ColorIndex = 3
                .Cells(lngI, 1).Font.Color = 2
            ElseIf lngA < lngB Then
                .Cells(lngI, 1).Interior.ColorIndex = 50
                .Cells(lngI, 1).Font.Color = 2
            End If
        End With
    Next lngI

End Sub

Upvotes: 2

Related Questions