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,082,177
Messages
5,363,591
Members
400,753
Latest member
Lizanz

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top