Avatar of Cartillo
Cartillo
Flag for Malaysia asked on

Highlight Cell with if Condition

Hi Experts,

I would like to request Experts help to modify the attached code. At present, the script highlights cell at  “Number” column in red if the value at Column “DataB” cell  less than value at Column “DataA” cell. Now need to modify the condition, highlight cell in red at “Number” column if value at “DataB” cell is more than 0 but less than value at “DataA” column. Hope Experts will help me to modify this code. Attached as well the workbook for Experts perusal.




Sub ColorNumber()
Dim myCols As Range
Dim myCell As Range

    For Each myCols In Range("A1", Range("A1").End(xlToRight))
    
        If myCols.Value = "Number" Then
        
            For Each myCell In Range(Cells(2, myCols.Column), Cells(Rows.Count, myCols.Column).End(xlUp))
                If myCell.Offset(0, 2).Value <> myCell.Offset(0, 3).Value Then
                    myCell.Interior.Color = 225
                     
                Else
                    myCell.Interior.Color = -33
                End If
            Next myCell
        End If
    Next myCols
End Sub

Open in new window

ColorCode.xls
Microsoft Excel

Avatar of undefined
Last Comment
Cartillo

8/22/2022 - Mon
StephenJR

Cartillo - at the moment that code appears to fill the cell if DataA does not equal DataB, not if DataB is less than DataA.
SOLUTION
Gemstorm

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
StephenJR

For your question as given though, change line 10 to

If myCell.Offset(0, 3).Value > 0 And myCell.Offset(0, 2).Value > myCell.Offset(0, 3).Value Then

Open in new window

Cartillo

ASKER
Hi StephenJR,

Thanks a lot for the revised code. Need one more help, highlight the  data in yellow if the DataB is 0. Hope you'll consider this request.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Cartillo

ASKER
Hi StephenJR,

Thanks for the help
Cartillo

ASKER
Hi StephenJR,

I need your help with this Q, kind of complicated to achieve the desired result.  

https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27249118.html