Case or If statement

[eznsd]

I have the following code to change a cell's color based on certain conditions. I am comparing the values of two cells on the same worksheet. I have tried this with If Else If, seperate If statements and Case statements but cannot get it to work. Any clues would be appreciated and Happy Holidays!

        Select Case value
            Case Range("N" & j).value = "0"
                Range("N" & j).Interior.Color = RGB(255, 0, 0)
                Range("N" & j).BorderAround Weight:=xlThin
                Range("N" & j).BorderAround xlContinuous
            Case Range("N" & j).value - Range("G" & j).value <= "100"
                Range("N" & j).Interior.Color = RGB(255, 192, 0)
                Range("N" & j).BorderAround Weight:=xlThin
                Range("N" & j).BorderAround xlContinuous
            Case Range("N" & j).value < Range("G" & j).value
                Range("N" & j).Interior.Color = RGB(255, 255, 0)
                Range("N" & j).BorderAround Weight:=xlThin
                Range("N" & j).BorderAround xlContinuous
        End Select

 

[MickG]

Perhaps:-

Select Case Range("N" & J).Value
            Case Is = "0"
                Range("N" & J).Interior.Color = RGB(255, 0, 0)
                Range("N" & J).BorderAround Weight:=xlThin
                Range("N" & J).BorderAround xlContinuous
            Case Is <= "100"
                Range("N" & J).Interior.Color = RGB(255, 192, 0)
                Range("N" & J).BorderAround Weight:=xlThin
                Range("N" & J).BorderAround xlContinuous
            Case Is < Range("G" & J).Value
                Range("N" & J).Interior.Color = RGB(255, 255, 0)
                Range("N" & J).BorderAround Weight:=xlThin
                Range("N" & J).BorderAround xlContinuous
        End Select

 

[eznsd]

That would make too much sense! I will give it a try and let you know shortly. Thanks for the reply Mick and Happy Holidays!

 

[eznsd]

This did not work. I will try to figure out another way to accomplish my goal.

 

[eznsd]

I wanted to let you know that I was able to modify your code and get it to work. This is the final code.

        Select Case Range("N" & j).value
            Case 0
                Range("N" & j).Interior.Color = RGB(255, 0, 0)
                Range("N" & j).BorderAround Weight:=xlThin
                Range("N" & j).BorderAround xlContinuous
            Case Is < Range("G" & j).value
                Range("N" & j).Interior.Color = RGB(255, 255, 0)
                Range("N" & j).BorderAround Weight:=xlThin
                Range("N" & j).BorderAround xlContinuous
            Case 1 To 100
                Range("N" & j).Interior.Color = RGB(255, 192, 0)
                Range("N" & j).BorderAround Weight:=xlThin
                Range("N" & j).BorderAround xlContinuous
        End Select

Thanks again for leading me in the right direction! Happy Holidays!