filling 2 cells with color based on one cells info?

[Ruff0r]

If cell a1 has a value of XX (XX will be filled with text, not numbers) is there a way to have excel search that row (b1 through bc1) for XX and fill that cell with yellow and the cell directly to the right with yellow.

I already use a Cell.Interior.ColorIndex and CountByColor to add up the cells that are filled yellow.

Thank You, please let me know if you need more information.

 

[Trevor G]

Look at this

Excel Workbook
	A	B	C	D	E	F
1	Test
2
3	Another			Test	Colour
4	Not today	Test	Colour
5	Where Else				Test	Colour
Sheet2

Using this to colour

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> findText()<br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("A3:F5")<br>    <SPAN style="color:#00007F">If</SPAN> cell.Value = Range("A1") <SPAN style="color:#00007F">Then</SPAN><br>        cell.Interior.Color = vbYellow<br>        cell.Offset(0, 1).Interior.Color = vbYellow<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">Next</SPAN> cell<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Ruff0r]

Your reply makes complete sense but for some reason it is not working. Below is the exact range and cells that I am trying to use this code with. Also I forgot to mention I need the same concept for range a3:ba3 - a17:ba17. Any help would be greatly appreciated!

Sub findText()
Dim cell As Range
For Each cell In Range("A2:BA2")
If cell.Value = Range("BF2") Then
cell.Interior.Color = vbYellow
cell.Offset(0, 1).Interior.Color = vbYellow
End If

Next cell

End Sub

 

[Trevor G]

Have you any conditional formatting

Sub findText()
Dim cell As Range
For Each cell In Range("A2:BA2,a3:ba3,a17:ba17")
If cell.Value = Range("BF2") Then
cell.Interior.Color = vbYellow
cell.Offset(0, 1).Interior.Color = vbYellow
End If
Next cell
End Sub

 

[Ruff0r]

Thank You very much, I got everything to work, I do not have any conditional Formatting I just forgot to run the macro.

 

[Trevor G]

Pleased to read it is working for you.