Highlight Certain Data Using Macros

**internetbum** · 08-17-2010, 12:09 PM

Hi,

I am an absolute novice with macros in Excel so was wondering if someone could help me.

Basically we have a set of data that we pull each month which is essentially a long list of numbers. Contained within the same cell as the numbers is a + or - plus a number of how much the figure has increased or decreased since the last month. For example:

56(+4)
76(-2)
102(-7)
36(+50)

I want to be able to highlight the numbers that are + in green and - in red. I can't seem to find a way to do this, if anyone could help I'd be very grateful!

Thanks

**Palmetto** · 08-17-2010, 12:30 PM

Adjust range reference to suit.

Option Explicit

Sub Highlight()

    Dim c As Range, lrow As Long, p1, p2
    
    lrow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    On Error Resume Next
    For Each c In Range("A1:A" & lrow)
        p1 = InStr(1, c.Value, "+", vbTextCompare)
        p2 = InStr(1, c.Value, "-", vbTextCompare)
        If p1 > 0 Then c.Interior.ColorIndex = 4 'green
        If p2 > 0 Then c.Interior.ColorIndex = 3 'red
        
    Next c

    Application.ScreenUpdating = True

End Sub

**Whizbang** · 08-17-2010, 02:44 PM

Just use conditional formatting.

1:Select your range.

2:Go to "Format" -> "Conditional Formatting"

3: Select the "Formula" option.

4: Enter the formula "=ISNUMBER(FIND("+",A1))" (without the quotes and assuming A1 is the active cell).

5: Select the format you want.

6: Click "Add >>"

7: Select the "Formula" option.

8: Enter the formula "=ISNUMBER(FIND("-",A1))" (without the quotes).

9: Select the format you want.

10: Click "Ok".