Conditional formatting in 03

[Rat1sully]

having a right mare of a time getting some conditional formatting working based on some data exported from access

basicall the range of data is D2:AC100

D2100 contains the value i want to base the formatting on

for each cell in the rest of the range E2:AC100
If the cell is empty do nothing
If the value in the corrisponding Column D for the row is as follows do associated formatting

=1, Red Background Red Text
2 <= value <= 5, Orange background/text
>5, Green background/text

edit the following 3 statements are the only thing I can get to work

=AND(NOT(E2=""),NOT(E2=0),$D2=1)
=AND(NOT(E2=""),NOT(E2=0),$D2>1,$D2<6)
=AND(NOT(E2=""),NOT(E2=0),$D2>5)

now i need to implement it in vba in a way that can run as a macro initiated from access

 

[NBVC]

Try these:

=AND(E2<>"",E2<>0,$D2=1)
=AND(E2<>"",E2<>0,$D2>1,$D2<=5)
=AND(E2<>"",E2<>0,$D2>5)

 

[Rat1sully]

how do i convert that into something in VBA that i can auto run on some data outputted from access?

Edit: written the following macro seems to work just doesn't look too neat

Sub Format()
Dim myRange As Range
Dim ref As Range
Set myRange = Range("E2:AC100")
Set ref = Range("D2:D100")

Dim iColor As Integer
Dim cell As Range
For Each cell In myRange
If cell.Value <> 0 Then
    If cell.Value <> "" Then
       If ref.Rows(cell.Row) = 1 Then
        cell.Interior.ColorIndex = 3 'Red
        cell.Font.ColorIndex = 3
        Else
       If ref.Rows(cell.Row) > 1 And ref.Rows(cell.Row) < 6 Then
        cell.Interior.ColorIndex = 46 'Orange
        cell.Font.ColorIndex = 46
        Else
       If ref.Rows(cell.Row) > 5 Then
        cell.Interior.ColorIndex = 4 'Green
        cell.Font.ColorIndex = 4
        Else
       End If
       End If
       End If
    End If
End If
Next
End Sub

 

[Rat1sully]

interesting it would appear that the above formats based on the D column from the row below, any ideas?