Conditional format VBA of cell based on criteria of another cell and formatting cell

EXCELISFUN1

New Member
Joined
Mar 11, 2019
Messages
6
I've been able to find almost everything in Mr.Excel to help me with VBA in the past but I'm stumbling over this one. I'm a VBA newby; learning by examples and "can I's" then googling.

I'm adding this bit to a macro that is working perfectly.

I have two columns (H & I). I need to conditionally format the cell in column I highlighting in yellow if the amount in that cell is less than 130. I only want to do this if the value in column H is <> to 0. I need to do this from row 10 until the last row of the data in column A.

I already have the last row indicator set as
"Dim i As Integer' and I have coding to determine the last row.

Thanks for any help; I'm sure it's an easy thing but I'm having brain cramps today.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Welcome to the Board!

Try this:
Code:
Sub MyCFMacro()

    Dim i As Long
    
'   Find last row with data in column A
    i = Cells(Rows.Count, "A").End(xlUp).Row

'   Set conditional formatting
    With Range("H10:H" & i)
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND($H10<>0,$I10<130)"
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
        End With
    End With

End Sub
 
Upvote 0
Thank you for so quickly responding. The code you provided is working but if I need the conditional formatting to be in column I would I just change the With Range("H10:H" & i) to With Range("I10:I" & i)? I'm sorry, I didn't make it clear in my original post that it's the column I data that is needing the highlighting if under 130. I appreciate the help!
 
Upvote 0
if I need the conditional formatting to be in column I would I just change the With Range("H10:H" & i) to With Range("I10:I" & i)?
Yes, you got it.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top