Visual Basic in Excel Question

I am trying to write some sort of code in VB to make Excel do something visually useful.

Every time I enter a number (x) greater than 0 in a cell I need it to turn the cell colour (not text) yellow to differentiate itself and make it stand out so that it is easy to find.

If I enter a positive number in the adjacent cell to the right (y), then the following needs to occur in the cell colour of the (y) cell:

If x=y, then y turns green
If x>y, then y turns blue
If x<y, then y turns red.

With me so far?

Now, let us further suppose that these two adjacent cells are in Columns A and B under the heading of “January”. The next two columns (C and D) are for “February” and thus independent of A and B. Therefore if I enter a value ‘z’ into Column C right beside the previous y value, it will turn yellow regardless of what is to its left.

(following that if in Column D value ‘q’ is entered, it is dependent on column C as per outlined above for A and B)

There is no restrictions on other cell entries that are not positive numbers. (which makes it easier).

Now… I have a small piece of code written up to deal with the first part (I think). However it breaks down obviously when dealing with the next set of columns.

This is not homework. I am trying to come up with a spreadsheet for my manager so that she can review only a few records on the sheet by scanning for entries and their associated results visually. (yellow was a positive result, green confirms, red means a retest error, blue means partial confirmation)

Here is what I have so far. Any help would be appreciated. Thanks guys. :slight_smile:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next
Target.FormatConditions.Delete

With Target.FormatConditions.Add(xlCellValue, xlGreater, 0)
    With .Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With
End With

For Each targetCell In Target.Cells
    If targetCell.Column &gt; 1 Then
        Dim nextDoorAddress As String
        
        nextDoorAddress = "$" & Chr(targetCell.Column + 63) & "$" & targetCell.Row
        Set nextDoorCell = Range(nextDoorAddress)
        
            targetCell.FormatConditions.Delete
        
        If targetCell.Value = nextDoorCell.Value Then

            With targetCell.FormatConditions.Add(xlCellValue, xlEqual, nextDoorCell.Value)
                With .Interior
                    .ColorIndex = 4
                    .Pattern = xlSolid
                End With
            End With

        Else

            With targetCell.FormatConditions.Add(xlCellValue, xlLess, nextDoorCell.Value)
                With .Interior
                    .ColorIndex = 33
                    .Pattern = xlSolid
                End With
            End With

            With targetCell.FormatConditions.Add(xlCellValue, xlGreater, nextDoorCell.Value)
                With .Interior
                    .ColorIndex = 3
                    .Pattern = xlSolid
                End With
            End With

        End If

    End If
Next targetCell

End Sub

Your life is going to be a hell of a lot easier if your version of Excel has conditional formatting.

Check under the Format menu for it.
If you have it, make a backup copy of the spreadsheet while you futz around with it.
It’s mostly self-explanatory, the colors are under the Pattern tab in the window that opens up when you click the Format button for each entry.

Highlight the X column, then do the first cell of the Y column, eliminating absolute cell references ($). Then use the Format Painter button (the little paintbrush right next to the Paste button on the Standard Toolbar) to get the rest of the Y column.

THEN, highlight all of the values in both columns, and use the Format Painter to drag across all the other two-column pairs you want to do this to.

I should add, for the Y column, add entries for all three possibilities. then, when you Fromat Paint it down the column, the correct option will kick in.

I agree with scotandrsn that conditional formatting is probably the way to go.

An observation: in Excel VBA if you are referring to cells relative to a known cell, use the “offset” property of the range object. It’s a lot easier to use than constructing range addresses. The “cells” property is a good one as well - both of these use row, column addressing, not the “A1” type addressing.