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

 

[Simon Lloyd]

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,Perhaps you could use a macro in the worksheet code module like this:

Code:
--------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Me.Range("L5").Interior.ColorIndex = 4 And _
Me.Range("Q5").Interior.ColorIndex = 4 And _
Me.Range("V5").Interior.ColorIndex = 4 Then
With Me.Range("AD5")
.Interior.ColorIndex = 4
.Value = 0
End With
End If
End Sub
--------------------




*How to Save a Worksheet Event Macro*
1. *Copy* the macro using *CTRL+C* keys.
2. Open your Workbook and *Right Click* on the *Worksheet's Name Tab*
for the Worksheet the macro will run on.
3. *Left Click* on *View Code* in the pop up menu.
4. *Paste* the macro code using *CTRL+V*
5. Make any custom changes to the macro if needed at this time.
6. *Save* the macro in your Workbook using *CTRL+S*


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Simon Lloyd]

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,Sorry made a mistake ther not reading your question properly, here's the

correct code:

Code:
--------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Me.Range("L5").Interior.ColorIndex = 4 And _
Me.Range("Q5").Interior.ColorIndex = 4 And _
Me.Range("V5").Interior.ColorIndex = 4 Then
With Me.Range("AD5")
.Interior.ColorIndex = 4
.Value = 1
End With
ElseIf Me.Range("L5").Interior.ColorIndex <> 4 Or _
Me.Range("Q5").Interior.ColorIndex = 4 Or _
Me.Range("V5").Interior.ColorIndex = 4 Then
With Me.Range("AD5")
.Interior.ColorIndex = 3
.Value = 0
End With
End Sub

--------------------



*How to Save a Worksheet Event Macro*
1. *Copy* the macro using *CTRL+C* keys.
2. Open your Workbook and *Right Click* on the *Worksheet's Name Tab*
for the Worksheet the macro will run on.
3. *Left Click* on *View Code* in the pop up menu.
4. *Paste* the macro code using *CTRL+V*
5. Make any custom changes to the macro if needed at this time.
6. *Save* the macro in your Workbook using *CTRL+S*


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[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

 

[Simon Lloyd]

What you also have to remember is if you are using conditional
formatting to colour L5, Q5 and V5 then this will not be picked up by
the code given.

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.

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[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.

 

[Simon Lloyd]

As i pointed out earlier the code given will not pick up condtional
formatting, are you also using data validation on those cells? (L5, Q5,
V5)

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.

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[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,

 

[Simon Lloyd]

You will know if you are using validation as when you click the cell you
get a dropdown arrow/box appear, anyway, are you using conditional
formatting to colour the original cells?

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,



Office Discussion' (http://www.thecodecage.com))
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
Code Cage Forums'
(http://www.thecodecage.com/forumz/showthread.php?t=104819)

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)