Change Colour of 3 Cells based on Value of Other Cell

Hi,

I'm trying to create a Macro which will change the interior colour of 3 cells based on the value of another cell. I can't use Conditional Formatting as our Sales force use Office 2003 and therefore I can only have 3(4) different rules however I need 5. I have tried many attempts and scoured the net however have had no luck so far. Here is my best attempt at a Macro so far:

Please Login or Register  to view this content.

You'll be able to see I am searching for a independent text string "baaa" and then once found offsetting by 8 cells to the right. I use independent text strings so that if somebody inserts a row it will still find and offset to the correct cell.

Does anyone see anything wrong with the above macro as it simply isn't working!

Kind Regards,
Harry Seager

It works for me. But why in the worksheet change event? Is B1=1?

Originally Posted by StephenR

It works for me. But why in the worksheet change event? Is B1=1?

The setup is as follows; I use a combo box which is linked to Cell B1. Therefore depending on what value is selected B1 will have a value from 1-5. Each different value from 1-5 needs a different colour and therefore I use 5 different macros ie. If B1=1 then..., if B1=2 then...

Unfortunately the code provided doesn't fulfill my needs... plus as a VBA amateur I'm struggling to understand what half of it means!

I use the search string 'baaa' to find the row location of where I would like highlighed a little like a Match function, then I offset by 8 columns to get to the box I need highlighting.

Thanks for your help so far.

Problem solved. Used the following Macro in the worksheet:

Please Login or Register  to view this content.