PLEASE HELP!! - Conditional Format - Formula not working

[NoeyPI]

Hello,
Please see my coding below. I can't get the formula in pink text to work. I would like the macro to apply a conditional formatting of red text to Column G if any of 4 specific columns contain a value that is equal to or in between input Values Cutoff 1 and Cutoff2.

For example, there are 4 columns which contain values of contract amounts. I have two input boxes which request the lowest contract amount and the highest contract amount I would like to query for. The first part of this macros works great. Within the specific four columns, it applies a red text to any of the values that are equal to and in between my lowest and highest amounts.

I would like to take this one step further and also apply a red text to Column G if ANY of the 4 columns contain a value that falls between the lowest and highest amounts. I'm new to Macros and I based myformula in pink text on the following:

=ISNUMBER(IF(OR(AND(AB:AB>=50000,AB:AB<=90000), AND(AG:AG>=50000,AG:AG<=90000), AND(AL:AL>=50000,AL:AL<=90000), AND(AQ:AQ>=50000,AQ:AQ<=90000)),1,""))"

This returns a True if I were to enter this in a cell and include the numbers I wants to search between.

I tried to "convert" this so that it would work with VBA and it looks like I'm missing something. I included an attachment with this post but I don't see it here so this is a link to a Pic of My Chart if you need a visual reference. https://ibb.co/kJ5dAc

If anyone can help me, I would greatly appreciate it. I've been obsessing over this and have tried multiple ways of changing this with no success.

THANK YOU IN ADVANCE!!!

Sub Values()
Application.ScreenUpdating = False


Dim Cutoff1 As String
Dim Cutoff2 As String
Dim myRange As Range
Dim myFormula As String


Cutoff1 = InputBox("Between This amount (enter lowest contract amount)")
Cutoff2 = InputBox("And This amount (enter highest contract amount)")

Set myRange = Range("AB:AB,AG:AG,AL:AL,AQ:AQ")

myRange.Cells.FormatConditions.Delete
Columns("G:G").Cells.FormatConditions.Delete

myRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=" & Cutoff1, Formula2:="=" & Cutoff2
myRange.FormatConditions(myRange.FormatConditions.Count).SetFirstPriority
With myRange.FormatConditions(1).Font
.Italic = False
.Bold = True
.Color = 255
.TintAndShade = 0
End With

myFormula = "=ISNUMBER(IF(OR(AND(AB:AB>=Cutoff1,AB:AB<=Cutoff2), AND(AG:AG>=Cutoff1,AG:AG<=Cutoff2), AND(AL:AL>=Cutoff1,AL:AL<=Cutoff2), AND(AQ:AQ>=Cutoff1,AQ:AQ<=Cutoff2)),1,""))"



Columns("G:G").FormatConditions.Add Type:=xlExpression, Formula1:=myFormula
Columns("G:G").FormatConditions(Columns("G:G").FormatConditions.Count).SetFirstPriority
With Columns("G:G").FormatConditions(1).Font
.Bold = True
.Color = 255
.TintAndShade = 0
End With
Columns("G:G").FormatConditions(1).StopIfTrue = False


ActiveSheet.Cells(ActiveCell.Row, 1).Select

Application.ScreenUpdating = True
End Sub

 

[Joe4]

I would like to take this one step further and also apply a red text to Column G if ANY of the 4 columns contain a value that falls between the lowest and highest amounts. I'm new to Macros and I based myformula in pink text on the following:

=ISNUMBER(IF(OR(AND(AB:AB>=50000,AB:AB<=90000), AND(AG:AG>=50000,AG:AG<=90000), AND(AL:AL>=50000,AL:AL<=90000), AND(AQ:AQ>=50000,AQ:AQ<=90000)),1,""))"

This are not valid checks:
AB:AB>=50000
You cannot check to see if any value in the column is greater than that number like that.

Instead of this:

[COLOR=#333333]AND(AB:AB>=50000,AB:AB<=90000)[/COLOR]

Replace with this:

COUNTIFS(AB:AB,">=50000",AB:AB,"=<90000")>0

You will need to do that for all four of your column checks.

 

[NoeyPI]

Hi Joe4! I hope all is well. You helped me once before - hopefully you can help with this too
The coding you referred to works in a worksheet as is. What I need to fix is this formula which bases the range on my input values. Any suggestions?

myFormula = "=ISNUMBER(IF(OR(AND(AB:AB>=Cutoff1,AB:AB<=Cutoff2), AND(AG:AG>=Cutoff1,AG:AG<=Cutoff2), AND(AL:AL>=Cutoff1,AL:AL<=Cutoff2), AND(AQ:AQ>=Cutoff1,AQ:AQ<=Cutoff2)),1,""))"

 

[Joe4]

The coding you referred to works in a worksheet as is.

Hmmm... You may want to try lots of different scenarios and double-check that.

When building formulas using variables, you need to combine a bunch of literal strings with your variables. The variables need to be outside of the double-quotes, or else they will be treated as literal string.
So you are going to have a bunch of pieces that you can sew together using &, i.e.

[COLOR=#EE82EE]myFormula = "=ISNUMBER(IF(OR(AND(AB:AB>=" & Cutoff1 & ",AB:AB<=" & Cutoff2 & "), AND(AG:AG>=" & Cutoff1 & ",AG:AG<=" & Cutoff2 & "), AND(AL:AL>=" & Cutoff1 & ",AL:AL<=" & Cutoff2 & "), AND(AQ:AQ>=" & Cutoff1 & ",AQ:AQ<=" & Cutoff2 & ")),1,""))"[/COLOR]

So, the format is:

="literal text" & variable & "literal text" & variable ...