Highlight Cell in one Column BASED on Conditional Formatting of Other Columns

[NoeyPI]

Hi!
This is my first time posting. I am a beginner with Macros. I was able to get most of what I wanted to do by searching through numerous message boards online but I'm stuck in one section. The code below applies a conditional format (orange highlight) to 6 specific columns based on whether those columns contain a specific text. I would like to add another step after this coding where the cells of another column (column B) are highlighted in a different color (yellow) if ANY of the 6 columns contain the orange highlighted conditional formatting.

So for example - In Row 2, if any of the 6 columns contain the conditional formatting of an orange highlight, then B2 will be highlighted in yellow.

Can anyone please help? I've look into examples with If and then statements but most of the examples online only deal with one column whereas in my example, the coding would have to check all 6 columns. I've been driving myself crazy trying to figure this out. Any help would be greatly appreciated!

Thanks

Sub SearchText()




Range("X:X,Z:Z,AE:AE,AJ:AJ,AO:AO,AT:AT").Select
Range("AT5").Activate
Selection.FormatConditions.Add Type:=xlTextString, String:="concrete", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

End Sub

 

[Joe4]

Not quite. It should look something like this:

    myFormula = "=ISNUMBER(SEARCH(" & Chr(34) & myValue & Chr(34) & ",X1&"",""&Z1&"",""&AE1&"",""&AJ1&"",""&AO1&"",""&AT1))"

 

[NoeyPI]

Not quite. It should look something like this:

    myFormula = "=ISNUMBER(SEARCH(" & Chr(34) & myValue & Chr(34) & ",X1&"",""&Z1&"",""&AE1&"",""&AJ1&"",""&AO1&"",""&AT1))"

Hi joe4,
Thank you so much for all your help!! I'm hoping you'll be able to help again. I thought I posted a reply yesterday but I don't see it here. Apologies if you're getting this twice.

I used the same format of the coding you helped me with. The only thing I need to modify is the formula listed below. I've modified the code to apply conditional formatting to any cell values within my range that fall between two values (Input Values 1 & 2). So for example - if I am searching for any cell values that are between 20,000 and 50,000, it would change the font color of those cells to red and bold. The next part of the coding uses the formula to then apply conditional formatting on the cells in Column G.

Can I somehow modify the formula below so that it find any values that are greater than or equal to Value 1 BUT less than or equal to Value 2 within the specified range? The formula currently searches for only the exact value of Value 1.

=ISNUMBER(SEARCH(" & Chr(34) & Value1 & Chr(34) & ",""&AB1&"",""&AG1&"",""&AL1&"",""&AQ1))"



Below I've cut and pasted my code

Sub Between_Values()


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

Value1 = InputBox("Between This amount (enter lowest contract amount)")
Value2 = InputBox("And This amount (enter highest contract amount)")

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

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

myFormula = "=ISNUMBER(SEARCH(" & Chr(34) & Value1 & Chr(34) & ",""&AB1&"",""&AG1&"",""&AL1&"",""&AQ1))"

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

End Sub