| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using 3 coloured Cells to Format a 4th Cell
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, |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using 3 coloured Cells to Format a 4th Cell
You will have to make use of VBA to acheive this...Right click on the
sheetView 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 --------------- Jacob Skaria "Ra" wrote: 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, |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using 3 coloured Cells to Format a 4th Cell
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" wrote: You will have to make use of VBA to acheive this...Right click on the sheetView 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 --------------- Jacob Skaria "Ra" wrote: 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, |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using 3 coloured Cells to Format a 4th Cell
To find out the right color, first color the cell Q5. Launch VBE using
Alt+F11 and from menu ViewImmediate 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 --------------- Jacob Skaria "Ra" wrote: 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" wrote: You will have to make use of VBA to acheive this...Right click on the sheetView 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 --------------- Jacob Skaria "Ra" wrote: 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, |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using 3 coloured Cells to Format a 4th Cell
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" wrote: To find out the right color, first color the cell Q5. Launch VBE using Alt+F11 and from menu ViewImmediate 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 --------------- Jacob Skaria "Ra" wrote: 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" wrote: You will have to make use of VBA to acheive this...Right click on the sheetView 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 --------------- Jacob Skaria "Ra" wrote: 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, |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using 3 coloured Cells to Format a 4th Cell
Are you sure macros are enabled. (Tools|Macro|Security).
The change happens during the selection change event. If this post helps click Yes --------------- Jacob Skaria "Ra" wrote: 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" wrote: To find out the right color, first color the cell Q5. Launch VBE using Alt+F11 and from menu ViewImmediate 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 --------------- Jacob Skaria "Ra" wrote: 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" wrote: You will have to make use of VBA to acheive this...Right click on the sheetView 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 --------------- Jacob Skaria "Ra" wrote: 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, |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using 3 coloured Cells to Format a 4th Cell
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. Ra;374917 Wrote: 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" wrote: You will have to make use of VBA to acheive this...Right click on the sheetView 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 --------------- Jacob Skaria "Ra" wrote: 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, -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=104819 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using 3 coloured Cells to Format a 4th Cell
Ra;374492 Wrote: 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's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=104819 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using 3 coloured Cells to Format a 4th Cell
Ra;374492 Wrote: 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) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=104819 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding sum of coloured cells | Excel Discussion (Misc queries) | |||
| sum coloured cells | Excel Discussion (Misc queries) | |||
| Coloured Cells | Excel Discussion (Misc queries) | |||
| Counting coloured cells | Excel Discussion (Misc queries) | |||
| Summing only coloured cells | Excel Worksheet Functions | |||
Hybrid Mode
