Conditional formating using VB

[mgf23]

Hi folks,

I would like to use VB code to conditionally format rows based on some specific conditions:

The cells in column C will contain a string of text.

Based on this; If the word ‘APPLES’ appears in that string of text and the word “LOCATION” appears in cell M
Cell E needs to be shaded red – whilst the rest of the cells of that row are shaded blue.

Obviously if the conditions are not met the formatting does not take place.

Could anyone advise on how to achieve this using vb code?

any help greatly received!

[davesexcel]

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[mgf23]

Thanks for the pointer. Here is an example.

[JosephP]

why do you want to use vba instead of regular conditional formats?

[mgf23]

Thinking about if there is a way to specify this using regular conditional formats I could use that - but again I am not sure how to do this

[Ezzard]

I have attached an edited version of your workbook that will highlight in the manner that you wish. It adds conditional highlighting based on the following formula:

Please Login or Register  to view this content.

This will check that both "apples" appears in column C and "location" appears in column M.

[mgf23]

Thanks for the input and this almost does the job. The final part i need is to fill cell E from that row in a different colour Not entirely sure if that's possible?

[Ezzard]

Cells in column E should already be highlighted in red as you showed in your example. If you go to Conditional Formatting > Manage Rules > Show formatting rules for: This Worksheet, there should be two separate formatting entries for highlighting the entire row in blue and column E in red. You can re-order the formatting and add/remove formatting options as you see fit. All you need to do is copy that formula into any new rules that you add.

[mgf23]

Perfect. works a treat! Thanks for your time!