Hello,

I would like to write a VBA macro with a button command to change the background colour of cells in the last active row in a range in sheet 2 Range (A1: A8) only if the cells in a range (A1:A5) in sheet 1 are the same as in sheet 2 .

As follow:

Sheet 1
A1 A2 A3 A4 A5
1 2 3 4 5

Sheet 2
A1 A2 A3 A4 A5 A6
12 3 1 7 2 9

If value in A1 sheet 1 in range A1: A6 in sheet 2 colour red else the blanks
If value in A2 sheet 1 in range A1: A6 in sheet 2 colour orange else the blanks
If value in A1 sheet 1 in range A1: A6 in sheet 2 colour yellow else the blanks
If value in A1 sheet 1 in range A1: A6 in sheet 2 colour blue else the blanks
If value in A1 sheet 1 in range A1: A6 in sheet 2 colour green else the blanks

Example:
In our example: A3 background in sheet 2 would be red
In our example: A5 background in sheet 2 would be orange
In our example: A2 background in sheet 2 would be yellow
The other cells in sheet 2 ( A 1, A4, A6 ) would stay the same

Can anyone help?

Thanks in advance

Bruno

Hi Bruno,
I think some aspects of your post contradict others but I have the picture.

Sheet 1 has values and sheet 2 has the look-up Range you colour based on.
For each myValue in myValues
For each myLookup in myLookups
If if myValue = my lookup then
MyValue.color = green
And so on and so on.

__________________
Nostalgia 4 Infinity

Hi Zakalwe,

Thank you very much indeed for taking the time to try to help me out
and apologies if my original explanation was unclear.

For each race I forecast 6 horses which I write in sheet 2 range ( A1:A6 )
when the race is run I enter the finish order of the first 5 horses in
sheet 1 range ( A1: A5). A1 being the the winner, A2 being the second etc.
All I really want is when entering the finish order for the 5 horses in sheet 1 range ( A1:A5) for the cells in sheet 2 range ( A1: A6 ) to colour in only if there are the same in sheet 1 and sheet 2.
That's to say
If A1 in sheet 1 features in sheet 2 range (A1: A6) then the colour should be red
If A2 in sheet 1 features in sheet 2 range (A1: A6) then the colour should be orange
If A3 in sheet 1 features in sheet 2 range (A1: A6) then the colour should be yellow
If A4 in sheet 1 features in sheet 2 range (A1: A6) then the colour should be
blue
If A5 in sheet 1 features in sheet 2 range (A1: A6) then the colour should be green

I hope this is much clearer.
I have tried to copy the code you sent me but unfortunately in vain.
I would very grateful if you could solve this out and tell me what code to copy into the command button for it to work.

Thanks in advance
Bruno

Hi,
I see, but I don't see the ranking in the your algorithm, is that because the order of the horses doesn't matter as long as all houses present?
I'll write the code for you tomorrow. Almost midnight in my country.

__________________
Nostalgia 4 Infinity