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.
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 > 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