how can i highliight cells that contain spisific data

[DeWaal Le Grange]

Hi there Excel experts

i need a vba code that will highlight all cells containing this word (discontinued) but it can be anything from "disco" upwords eg. "disco","discon",discont",disconti",discontin" and so on...

My range of data starts from Cells "B37:B65536" when anyone of the aboved mention is found it must be highlighted "Green" it must also highlite the cell before it in column A and the cell after it in column C.

eg.

Column A /ColumnB /Column C
green / silicone discont /green

the vba i have looks like this but doesnt do what it is uppose to.

Sub highlight()
Dim cell As Range
Range(Range("a37:c65536"), Range("a37:c65536").End(xlDown)).Select
For Each cell In Selection
If cell = "disc" Then cell.Cells.Interior.ColorIndex = 4
Next cell
End Sub


Please help me out on this...

Thanks Dewaal

 

[Macropod]

Hi Dewaal,

This question should have been posted in the Excel forum, which is where it'll probably end up as soon as the mods get hold of it.

In any event, you don't need vba for what you want - you can use Conditional Formatting. For example, the Conditional Formatting formula =SEARCH("DISCO",A37)>0 will find any string containing the 'DISCO' sequence, whether in upper or lower case, in A37. Thus, It will find any of the combinations you listed, plus words like "discovered" and "undiscovered".

All you need to do to highlight A37 is to insert the Conditional Formatting formula in that cell and apply the green Conditional Formatting format. From there, it's a simple matter to use copy & Edit|Paste Special|Format to extend the conditional format to the rest of your range.

 

[DeWaal Le Grange]

Hi Macropod

thank you for the reply, im so stuck, i dont see this working for my whole range... if any cell content in range B37:B65536 is = or > than the word "disco" it must be highlited green. as well as the cells on the right and on the left. eg.

if B37 is "silicone discont" it must hihglight A37, B37, & C37 (Green)

i dont think i understand how i can use conditional formating...

any advises

dewaal

 

[Macropod]

Hi ,

If the cells being tested are only in column B, the Conditional Formatting formula:
=SEARCH("DISCO",$B37)>0
will do the job for all three columns. Give it a try.

 

[DeWaal Le Grange]

Macropod

You are a genius… thanks a million… funny that a $ sign can change things… hehe

it works 100%

Have a fab day

dewaal