VBA Conditional formatting help

[x_swinson_x]

This is the code that I have for conditional formatting. What I want to happen is as follows

Cells that contain numbers between 0 and 1 to be red
Cells that contain numbers greater than 1 and less than 100 to be yellow
Cells that contain numbers equal to 100 to be green

Here's the current code I'm working with:

Dim d As DoubleDim r As Range
Set r = ActiveSheet.Range("B15:AF38")
For Each Cell In r
If Cell.Text <> "" And IsNumeric(Cell.Value) = True Then
If Cell.Value > 0 And Cell.Value < 1 Then
Cell.Interior.Color = RGB(255, 0, 0)
If Cell.Value > 100 Then
Cell.Interior.Color = RGB(0, 255, 0)
If Cell.Value > 1 And Cell.Value < 100 Then
Cell.Interior.Color = RGB(255, 255, 0)
End If
End If
End If
End If


Next

Currently the code isn't changing any of the cell backgrounds. Any advice?

Excel 2016, Windows 7

 

[x_swinson_x]

Red is working but the other colors are not

 

[Special-K99]

This

Dim d As DoubleDim r As Range

should surely be this

Dim d As Double
Dim r As Range

???

 

[Special-K99]

You need

If... Then
Else

Your Yellow and Green conditions are dependent on your Red condition

 

[Special-K99]

This works

Sub k1()
Dim d As Double
Dim r As Range
Set r = ActiveSheet.Range("A1:A10")
For Each Cell In r
If Cell.Text <> "" And IsNumeric(Cell.Value) = True Then

    If Cell.Value > 0 And Cell.Value < 1 Then
    Cell.Interior.Color = RGB(255, 0, 0)
    End If
    If Cell.Value > 100 Then
    Cell.Interior.Color = RGB(0, 255, 0)
    End If
    If Cell.Value > 1 And Cell.Value < 100 Then
    Cell.Interior.Color = RGB(255, 255, 0)
    End If
End If
Next
End Sub

 

[x_swinson_x]

This

Dim d As DoubleDim r As Range

should surely be this

Dim d As Double
Dim r As Range

???

Error when copying to the thread. It's correct in VBA. Still having the same issue.

 

[Special-K99]

See post #5

 

[x_swinson_x]

This works

Sub k1()
Dim d As Double
Dim r As Range
Set r = ActiveSheet.Range("A1:A10")
For Each Cell In r
If Cell.Text <> "" And IsNumeric(Cell.Value) = True Then

    If Cell.Value > 0 And Cell.Value < 1 Then
    Cell.Interior.Color = RGB(255, 0, 0)
    End If
    If Cell.Value > 100 Then
    Cell.Interior.Color = RGB(0, 255, 0)
    End If
    If Cell.Value > 1 And Cell.Value < 100 Then
    Cell.Interior.Color = RGB(255, 255, 0)
    End If
End If
Next
End Sub

This worked perfectly thanks so much!