Highlight Cell If Macro

[ScabbyDog]

I want to look at Column D on the sheet 'Furniture' from row 2 down as far as the last used data in row 2 and if the the cell contains the word "Multi" then I want it to highlight the cell in light red.

[duncandhu]

Use conditional formatting on column D? Or do you need to only perform this up to last used data?

Regards
Duncan

[ScabbyDog]

Conditional formatting will not work as my data is dirty and gets changed every day so therefore cells get inserted and deleted and this alters the conditional formatting. I need a macro that is used whenever the sheet is opened and this solves my issue.

[BigBas]

This is some quick and dirty code. Let me know if it helps.

Please Login or Register  to view this content.

[ScabbyDog]

Mhmm, doesn't quite do the trick. Is there a different way of coding it as I'm sure we don't need to use InStr here.

[BigBas]

Any particular reason you'd rather not use InStr?

This following method use a filter instead. Let me know if it works

Please Login or Register  to view this content.

[ScabbyDog]

Don;t want to use InStr as I will need to add a couple of words to it. In total, there will be up to 6 different words that if contained in the cell I'll want to highlight red. If this can be achieved using InStr then I will be happily proven wrong

[BigBas]

Gotcha. Use the second code I gave you, (the one with the filter) and change criteria1 to the following:

Please Login or Register  to view this content.

Replace the XXXX with the values you want to search.

Let me know if this helps

[ScabbyDog]

Sorry to be a pain again, but I don't want to use a filter as this macro I need will go in amongst many other macros which will have the data very messy and running a filter would cause quite a delay/non-responsiveness. Is there a loop or something like I've been playing below with that would work:

The below highlights what I want if the date is today. Can it be altered for what I want above?

Please Login or Register  to view this content.

[duncandhu]

Expanding on BigBas' idea:

We can total the InStr result for each word (stored in an array) and then if the total > 0, change the cell color. The idea being that if one or more of the words is found, total will be greater than 0.

Please Login or Register  to view this content.

[WasWodge]

I don't want to use a filter as this macro I need will go in amongst many other macros which will have the data very messy and running a filter would cause quite a delay/non-responsiveness. Is there a loop or something like I've been playing below with that would work:

1st of all try the code in post #10

But what I am interested in is why you think running a filter even multiple times would have the effect below

running a filter would cause quite a delay/non-responsiveness

more than a loop?