Find cell with text, and change background color...

[spacely]

Hello,

I need a small VBA routine that finds every cell in column A that has the text "Donut" included in it to have its background color changed to another color, like red. A general color would be best like R,G,B values to make it anything.

Thanks a lot!

David

 

[Rick Rothstein]

I need a small VBA routine that finds every cell in column A that has the text "Donut" included in it to have its background color changed to another color, like red. A general color would be best like R,G,B values to make it anything.

Give this macro a try...

Sub MakeDonutRed()
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.Color = RGB(255, 0, 0)
  Columns("A").Replace "*donut*", "", , , False, , False, True
  Application.ReplaceFormat.Clear
End Sub

 

[inactiveuserps07]

You could also accomplish this pretty easily with basic conditional formatting:

1. Highlight Column A.
2. From the Home tab, click Conditional Formatting
3. Click "New Rule" > "Format only cells that contains ..."
4. Under "Format only cells with:" enter "Specific text" [in the first drop-down] ... "containing" [in the second drop-down] ... "donut" [type this in]
5. Click "Format..." and select the "Fill" tab. Choose your color.
6. Click "OK" ... "OK"

 

[spacely]

Thanks Rick. That works.