Using 3 coloured Cells to Format a 4th Cell

[Ra]

Please can you help me with the following problem:

I have three cells: L5, Q5 and V5, which when I colour green, then I would
like for cell AD5 to be automatically coloured green and a number 1 be placed
in it.

If on the other hand cells L5, Q5, V5 are any other colour, then cell AD5
should be coloured red and a zero to be placed in it.

Please can you help me to devise a method of doing this.

Many thanks for your help and support,

 

[Jacob Skaria]

You will have to make use of VBA to acheive this...Right click on the
sheet>View and paste the below code. If you are new to macros set the
Security level to low/medium in (Tools|Macro|Security).

The color referred here is 'Bright Green'. Change the color index as needed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Range("L5").Interior.ColorIndex = 4 And _
Range("Q5").Interior.ColorIndex = 4 And _
Range("V5").Interior.ColorIndex = 4 Then
Range("AD5").Interior.ColorIndex = 4
Range("AD5") = 1
Else
Range("AD5").Interior.ColorIndex = 3
Range("AD5") = 0
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes

 

[Ra]

Jacob

Many thanks for your help. I have placed the macro under module1, however,
even though the cells are green (?) I obtain a red in cell AD5 with a zero.
Just one point the green cells for L5, Q5, V5 are generated using conditional
formatting. I am not sure if this is a bright green (4). Perhaps, this is
what is generating the red in cell AD5. How can I find out if I am using the
right colour?

Thank you.

 

[Jacob Skaria]

To find out the right color, first color the cell Q5. Launch VBE using
Alt+F11 and from menu View>Immediate Window Or (Ctrl+G). In immediate window
type

?Range("Q5").Interior.ColorIndex

and Enter; that will return the color index. In the below code change all 4
to this new color index. The change happens during the selection change
event. Try and feedback.


If this post helps click Yes

 

[Ra]

Hi Jacob

The colour is correct. It is number 4. However, I am still unable to get
the conditionally formatted (green (4) coloured cells) L5, Q5, V5 to be
picked up in cell AD5 and get a result of green with a number 1 in it. In
addition, I want all the cells below L5, Q5, V5 to be applied to the cells
below AD5, in the same way. I hope this makes sense.

Many thanks,

 

[Jacob Skaria]

Are you sure macros are enabled. (Tools|Macro|Security).

The change happens during the selection change event.

If this post helps click Yes

 

[Ra]

Hi Jacob,

I am really sorry but I cannot get this to work. Let me try to spell out
exactly what I am doing, please bear with me:

1. I am using excel 2007
2. Macros are enabled.
3. I have taken your code and using the "View Code" on sheet1, I have
placed the code in the opened macro window.
4. In the top of the macro window, left box = Worsheet, in the right box=
SelectionChange
5. I have saved the macro in the window using "CTRL S".
6. I exit and return to sheet1.
7. On clicking on AD5, I have a red cell without any numbers in the cell.

Further information:

L5, Q5 and V5 have condtional formatting which generate 3 green cells and
three dates. So this should create a green cell in AD5 with a number 1 in
it. As I said, I am getting a red cell in AD5 with a zero. I would like to
apply this macro to the rest of the cells underneath L5,Q5,V5 and AD5;
hopefully, if I can get the above resolved.

In addition, I have several other macros running under "ThisWorkbook".

I hope this is a better explanation of what is going on.

Many thanks once again for your help and support.

 

[Ra]

Hi Simon

As far as I know, I am not using data validation on those cells. Will that
make a difference? To be honest I don't know whether I am or not!

Many thanks for your help,