VBA Conditional Formatting using wildcard

Hi,

Not as far as I'm aware.

I'd be inclined to use an initiial IF test. i.e.

Please Login or Register  to view this content.

Hi,

This should work...

Please Login or Register  to view this content.

If you are not keen on using the "calculative" way in the Case statement, I think the only other way is to replace the Select case with an If...ElseIf...End If structure and use the condition similar to:

Please Login or Register  to view this content.

Originally Posted by slohman

Thankyou so much for all your help. Works like a dream but a little slow could you give any pointers of how to make it go faster.

Hi,

Given that you have Excel 2007 and 64 levels of conditional format why are you using VBA anyway. VBA is generally always slower than normal Excel functionality so why not use cond. formats?

I second Richards reply.
My initial response to your question was to help with the wildcard search, but based on the code you posted and the extra search and formatting you are doing, I'd tend to go the conditional formatting route. Its very flexible, easy to modify and will by fast and dynamic on the sheet. It's worth considering and experimenting with it. I'm not sure if there is much else we can do to the code to speed it up further.

Maybe I can ask: Is it necessary to scan every cell in the range P5:BS700? Do these terms occur in ANY cell in this range or is it only specific columns. If we can minimize the amount of cells it must scan, it can significantly increase the speed.

If you are bringing in data from another sheet or workbook, you could also experiment with the following to avoid issues with CF:

Scenario 1: New data between existing data...
Insert new cells or rows between the existing data that has CF (the data you copied will be pasted into these new cells/rows)
Paste (as values) the copied data into these new cells, so that it does not overwrite the CF in the existing cells.
The CF should kick in straight away since the CF is applied to cells/rows inserted between existing formatting.

Scenario 2: New data below existing data...
Paste the new copied data below the existing rows that have CF
Activate the CF dialog and adjust the range to include the new added data. (See image below)

1.jpg