This is my first post so I apologize if I am breaking any protocols. If it makes any difference, I have only been learning/playing around with VBA for about 2 weeks so everything that is in my code is what I have picked up from examples.

I am attempting to use VBA to apply conditional formatting to a certain range based on the value in a different column. My selected range has dollar values, and the other range has a percentage variance from the approved budget. So if the percentage is > 0% and < 10% I want to use RGB (255,192,0) and if the percentage is >= 10% I want to use RGB(255,0,0). So far I have been able to select my desired range and to find the relative reference between the two columns. I seem to be stuck on the conditional format statement(s).

Thanks in advance for any and all replies.

Sub Over_Budget()

    Dim Total_Pro_Budget As Range
    Dim end_row_text As String
    Dim end_row_numb As Double
    Dim TPB_col_text As String
    Dim TPB_col_numb As Double
    Dim TPB_row_text As String
    Dim TPB_row_numb As Double
    Dim per_col_text As String
    Dim per_col_numb As Double
    Dim per_row_text As String
    Dim per_row_numb As Double
    Dim TPB_to_per As String
    
    
    TPB_to_per = Range("Total_Project_Budget").Address(ReferenceStyle:=xlR1C1, _
        rowabsolute:=False, _
        columnabsolute:=False, _
        relativeto:=Range("Percentage"))
        
    end_row_text = Range("end").Address(ReferenceStyle:=xlR1C1)
    
    end_row_numb = Mid(end_row_text, 2, InStr(1, end_row_text, "C") - 2)
    
    TPB_col_text = Range("Total_Project_Budget").Address(ReferenceStyle:=xlR1C1)
    
    TPB_col_numb = Mid(TPB_col_text, InStr(1, TPB_col_text, "C") + 1, Len(TPB_col_text) - InStr(1, TPB_col_text, "C"))
    
    TPB_row_text = Range("Total_Project_Budget").Address(ReferenceStyle:=xlR1C1)
    
    TPB_row_numb = Mid(TPB_row_text, 2, InStr(1, TPB_row_text, "C") - 2)
    
    per_col_text = Range("Percentage").Address(ReferenceStyle:=xlR1C1)
    
    per_col_numb = Mid(per_col_text, InStr(1, per_col_text, "C") + 1, Len(per_col_text) - InStr(1, per_col_text, "C"))
    
    per_row_text = Range("Percentage").Address(ReferenceStyle:=xlR1C1)
    
    per_row_numb = Mid(per_row_text, 2, InStr(1, per_row_text, "C") - 2)
    
    
    ActiveWorkbook.Names.Add Name:="TPB_Range", RefersToR1C1:="=r" & TPB_row_numb + 1 & "c" & TPB_col_numb & ":r" & end_row_numb - 1 & "c" & TPB_col_numb
    
    Range("TPB_Range").Select
    
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
            Formula1:=">0", Formula2:="<10%"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            '.PatternColorIndex = xlAutomatic - Code seems to work with or without this line
            .Color = RGB(255, 192, 0)
            '.TintAndShade = 0 - Code seems to work with or without this line
        End With
            
        Selection.FormatConditions(1).StopIfTrue = False
        
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
            Formula1:="=100", Formula2:="=today()"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            '.PatternColorIndex = xlAutomatic - Code seems to work with or without this line
            .Color = RGB(255, 0, 0)
            '.TintAndShade = 0 - Code seems to work with or without this line
        End With
    

End Sub