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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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
 

EXCELISFUN1

New Member
Joined
Mar 11, 2019
Messages
6
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!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,906
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top