Need formula to format cells

[gspreeman]

I need to include in one of my macros code that will look in Column A for a cell that contains "TOTAL SALES" and then colors the interior of the cell a medium gray, applies Bold to the font, and applies a line on top and on the bottom of the cell. After this is done, I then need to apply that same formatting to the cells in that same row up to column R. Help! Thanks in advance.

 

[vletm]

gspreeman
Please reread Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

especially How to get the Best Results at Chandoo.org
After that, ask from Yourself
- Could You do something better to get answers?

 

[gspreeman]

Better title?

 

[gspreeman]

Here is what I have so far and, it kind of works but, I noticed that it applies the font change and interior color change to all cells that contain the word Sales. And, it doesn't include the top and bottom borders I need. I am new to scripting so, I'm not sure if formatting adjacent cells with the same formatting would be part of the same loop or a second establishment of ranges. Again, thank you in advance for any help you might be able to offer.

>>> use code - tags <<<

With Range("A1:A300").FormatConditions.Add(xlTextString, TextOperator:=xlContains, String:="Sales")
With .Font
.Bold = True
.Color = vbBlack
End With
.Interior.ColorIndex = 16
End With

 

[vletm]

gspreeman
Okay
... the title could be better too.
Did You continue to read other sentence from How to get the Best Results at Chandoo.org -part?
How could others test/verify something without ... ?

 

[p45cal]

try:

With Range("A1:R300").FormatConditions
  .Delete
  With .Add(Type:=xlExpression, Formula1:="=ISNUMBER(FIND(""TOTAL SALES"",$A1))")
    .Font.Bold = True
    .Borders(xlTop).LineStyle = xlContinuous
    .Borders(xlBottom).LineStyle = xlContinuous
    .Interior.Color = 11184814
  End With
End With

 

[gspreeman]

Works perfectly. Thank you so much!!!!!!!!!!!!!!!