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

N

NoeyPI

New Member
Joined
Dec 29, 2017
Messages
9
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
Welcome to the Board!

Conditional Formatting doesn't work off of other conditional formatting, but you can incorporate the rules from the other.
So, you could just check those fix columns like this:
Code: 
=OR(X5="concrete",Z5="concrete",AE5="concrete",AJ5="concrete",AO5="concrete",AT5="concrete")
 
Upvote 0
Joe4 said:
Welcome to the Board!

Conditional Formatting doesn't work off of other conditional formatting, but you can incorporate the rules from the other.
So, you could just check those fix columns like this:
Code: 
=OR(X5="concrete",Z5="concrete",AE5="concrete",AJ5="concrete",AO5="concrete",AT5="concrete")
Click to expand...


Hi Joe4,
Thanks for responding! I wasn't able to get your code to work. I also modified my coding a bit to include an Input Value. Is there a similar formula which would work with an input value instead of including the search term within the formula? I don't know whether the solution lies in a specific formula or if I need to use an if and then statement instead.

I've searched other online posts and there does seem to be a way to check whether a cell has conditional formatting by searching for a cell's Formatted Interior Color. I'm trying to create an If and then statement which checks all 6 columns per row and if I can get the specific row number - maybe I can apply the yellow highlighting to the 2nd cell of that row number. Below is the modified text.

Please let me know if you have any further suggestions.

Thanks

Range("X:X,Z:Z,AE:AE,AJ:AJ,AO:AO,AT:AT").Select


myValue = InputBox("Type search term for conditional formatting")

Range("X:X,Z:Z,AE:AE,AJ:AJ,AO:AO,AT:AT").Select
Range("AO1").Activate
ActiveWindow.SmallScroll ToRight:=1
Range("X:X,Z:Z,AE:AE,AJ:AJ,AO:AO,AT:AT").Select
Range("AT1").Activate
ActiveWindow.SmallScroll ToRight:=2

Selection.FormatConditions.Add Type:=xlTextString, String:=myValue, _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
 
Upvote 0
Assuming that you are looking for text values (strings), you can do this using the method I proposed. Here is what that code looks like (I just amended to the bottom of your code, after cleaning it up a bit):
Code: 
Sub MyFormat()

    Dim myValue As Variant
    Dim myRange As Range
    Dim myFormula As String
    
    myValue = InputBox("Type search term for conditional formatting")

    Set myRange = Range("X:X,Z:Z,AE:AE,AJ:AJ,AO:AO,AT:AT")

    myRange.FormatConditions.Add Type:=xlTextString, String:=myValue, _
        TextOperator:=xlContains
    myRange.FormatConditions(myRange.FormatConditions.Count).SetFirstPriority
    With myRange.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
        
    myFormula = "=OR(X1=" & Chr(34) & myValue & Chr(34) & ",Z1=" & Chr(34) & myValue & Chr(34) & _
                ",AE1=" & Chr(34) & myValue & Chr(34) & ",AJ1=" & Chr(34) & myValue & Chr(34) & _
                ",AO1=" & Chr(34) & myValue & Chr(34) & ",AT1=""concrete"")"

    Columns("B:B").FormatConditions.Add Type:=xlExpression, Formula1:=myFormula
    Columns("B:B").FormatConditions(Columns("B:B").FormatConditions.Count).SetFirstPriority
    With Columns("B:B").FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
    End With
    Columns("B:B").FormatConditions(1).StopIfTrue = False

End Sub
 
Upvote 0
Joe4 said:
Assuming that you are looking for text values (strings), you can do this using the method I proposed. Here is what that code looks like (I just amended to the bottom of your code, after cleaning it up a bit):
Code: 
Sub MyFormat()

    Dim myValue As Variant
    Dim myRange As Range
    Dim myFormula As String
    
    myValue = InputBox("Type search term for conditional formatting")

    Set myRange = Range("X:X,Z:Z,AE:AE,AJ:AJ,AO:AO,AT:AT")

    myRange.FormatConditions.Add Type:=xlTextString, String:=myValue, _
        TextOperator:=xlContains
    myRange.FormatConditions(myRange.FormatConditions.Count).SetFirstPriority
    With myRange.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
        
    myFormula = "=OR(X1=" & Chr(34) & myValue & Chr(34) & ",Z1=" & Chr(34) & myValue & Chr(34) & _
                ",AE1=" & Chr(34) & myValue & Chr(34) & ",AJ1=" & Chr(34) & myValue & Chr(34) & _
                ",AO1=" & Chr(34) & myValue & Chr(34) & ",AT1=""concrete"")"

    Columns("B:B").FormatConditions.Add Type:=xlExpression, Formula1:=myFormula
    Columns("B:B").FormatConditions(Columns("B:B").FormatConditions.Count).SetFirstPriority
    With Columns("B:B").FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
    End With
    Columns("B:B").FormatConditions(1).StopIfTrue = False

End Sub
Click to expand...


Joe4 - First of all, that fact that you're responding is a lifeline lol. I copied the formula and placed it in my code but nothing is happening to Column B - no highlighting. Below I copied the whole coding exactly as I have it. Any ideas? This is the final step I need. I really appreciate you helping.

FYI, I have this macro connected to a button on my spreadsheet so the first line formatconditions delete allows me to start fresh every time I need to search for a new term.

Cells.FormatConditions.Delete

Dim myValue As Variant
Dim myRange As Range
Dim myFormula As String

myValue = InputBox("Type search term for conditional formatting")


Set myRange = Range("X:X,Z:Z,AE:AE,AJ:AJ,AO:AO,AT:AT")


myRange.FormatConditions.Add Type:=xlTextString, String:=myValue, _
TextOperator:=xlContains
myRange.FormatConditions(myRange.FormatConditions.Count).SetFirstPriority
With myRange.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With

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


Columns("B:B").FormatConditions.Add Type:=xlExpression, Formula1:=myFormula
Columns("B:B").FormatConditions(Columns("B:B").FormatConditions.Count).SetFirstPriority
With Columns("B:B").FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
End With
Columns("B:B").FormatConditions(1).StopIfTrue = False
 
Upvote 0
Can you give me an example of what the data looks like on your sheet, and what value you are looking for?
I am most interested in whether or not the value you are looking for matches EXACTLY, or is just contained within the string.
 
Upvote 0
bJ4ZSG
Joe4 said:
Can you give me an example of what the data looks like on your sheet, and what value you are looking for?
I am most interested in whether or not the value you are looking for matches EXACTLY, or is just contained within the string.
Click to expand...

The value is just contained within the string. I've included an attachment. You can see the first part is working well. In this case, I searched for the word plumbing. It highlighted all of the cells that contained that text. What I would like to accomplish is to highlight the row in Column B if any of the 6 columns contain the search term.
bJ4ZSG
 
Upvote 0
NoeyPI said:
bJ4ZSG


The value is just contained within the string. I've included an attachment. You can see the first part is working well. In this case, I searched for the word plumbing. It highlighted all of the cells that contained that text. What I would like to accomplish is to highlight the row in Column B if any of the 6 columns contain the search term.
bJ4ZSG
Click to expand...


I don't know if the image uploaded - here's the link to the image: https://ibb.co/bJ4ZSG
 
Upvote 0
See if this code does what you want:
Code: 
Sub MyFormat()

    Dim myValue As Variant
    Dim myRange As Range
    Dim myFormula As String
    
    myValue = InputBox("Type search term for conditional formatting")

    Set myRange = Range("X:X,Z:Z,AE:AE,AJ:AJ,AO:AO,AT:AT")

    myRange.FormatConditions.Add Type:=xlTextString, String:=myValue, _
        TextOperator:=xlContains
    myRange.FormatConditions(myRange.FormatConditions.Count).SetFirstPriority
    With myRange.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
        
    myFormula = "=ISNUMBER(SEARCH(" & Chr(34) & myValue & Chr(34) & ",X1&Z1&AE1&AJ1&AO1&AT1))"
    
    Columns("B:B").FormatConditions.Add Type:=xlExpression, Formula1:=myFormula
    Columns("B:B").FormatConditions(Columns("B:B").FormatConditions.Count).SetFirstPriority
    With Columns("B:B").FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
    End With
    Columns("B:B").FormatConditions(1).StopIfTrue = False

End Sub
Basically, I am concatenating all six columns together, and looking for that string in there. There is a slight chance of a false positive (like if you are looking for "plumbing" and one cell ends with "plumb" and the next begins with "ing". If that is a real concern, we can amend our formula to concatenate commas between the six values it is combining.
 
Upvote 0
Joe4 said:
See if this code does what you want:
Code: 
Sub MyFormat()

    Dim myValue As Variant
    Dim myRange As Range
    Dim myFormula As String
    
    myValue = InputBox("Type search term for conditional formatting")

    Set myRange = Range("X:X,Z:Z,AE:AE,AJ:AJ,AO:AO,AT:AT")

    myRange.FormatConditions.Add Type:=xlTextString, String:=myValue, _
        TextOperator:=xlContains
    myRange.FormatConditions(myRange.FormatConditions.Count).SetFirstPriority
    With myRange.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
        
    myFormula = "=ISNUMBER(SEARCH(" & Chr(34) & myValue & Chr(34) & ",X1&Z1&AE1&AJ1&AO1&AT1))"
    
    Columns("B:B").FormatConditions.Add Type:=xlExpression, Formula1:=myFormula
    Columns("B:B").FormatConditions(Columns("B:B").FormatConditions.Count).SetFirstPriority
    With Columns("B:B").FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
    End With
    Columns("B:B").FormatConditions(1).StopIfTrue = False

End Sub
Basically, I am concatenating all six columns together, and looking for that string in there. There is a slight chance of a false positive (like if you are looking for "plumbing" and one cell ends with "plumb" and the next begins with "ing". If that is a real concern, we can amend our formula to concatenate commas between the six values it is combining.
Click to expand...

YES, YES, YES!!! It worked! Thank you soooooooooooooooooo much!! I would like to have the option of concatenating the commas between the six values - I modified your formula slightly - is this how I would add the commas

=ISNUMBER(SEARCH(" & Chr(34) & myValue & Chr(34) & ",X1&","&Z1&","&AE1&","&AJ1&","&AO1&","&AT1))
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Conditional Formatting - Highlighting Rows with Matching Cells in Multiple Columns
2
Replies
13
Views
517
ScottTemple
S
L
  • Question
Conditional Formatting - Highlight Rows with the Top 3 Risks
Replies
3
Views
280
SanjayGMusafir
SanjayGMusafir
O
  • Solved
Conditional Highlighting Cells by Fiscal Quarters?
2
Replies
17
Views
802
awoohaw
awoohaw
W
  • Solved
VBA Apply Formatting Based on 2 Criteria and If Another Cell Contains...
2
Replies
13
Views
274
Joe4
Joe4
E
  • Question
Highlight all cells in a row, that contain value in first column
Replies
1
Views
372
etaf
etaf

Forum statistics

Threads
1,215,200
Messages
6,123,612
Members
449,109
Latest member
Sebas8956

Share this page

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