Avatar of 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
                    myCell.Interior.Color = -33
                End If
            Next myCell
        End If
    Next myCols
End Sub

Open in new window

Microsoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon

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

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
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

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


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.
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.

Hi StephenJR,

Thanks for the help

Hi StephenJR,

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