Case or If statement

eznsd

New Member
Joined
Oct 29, 2015
Messages
42
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!
Code:
        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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Perhaps:-
Code:
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

New Member
Joined
Oct 29, 2015
Messages
42
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

New Member
Joined
Oct 29, 2015
Messages
42
This did not work. I will try to figure out another way to accomplish my goal.
 

eznsd

New Member
Joined
Oct 29, 2015
Messages
42
I wanted to let you know that I was able to modify your code and get it to work. This is the final code.
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!
 

Forum statistics

Threads
1,085,864
Messages
5,386,404
Members
401,997
Latest member
cizwiz

Some videos you may like

This Week's Hot Topics

Top