| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colored Cell
I need to create a macro that will display a color in a cell depending on the
condition of a different cell. For example if cell B1 has a 0 in it, A1 will turn red, otherwise it wont have a color. This needs to happen in about 4000 consecutive cells. Probably be easiest if it just happens when the workbook is opened. Thanks in advance for any help. -- Message posted via http://www.officekb.com |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colored Cell
Hi brownti
You can use CF See http://www.contextures.com/xlCondFormat01.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "brownti via OfficeKB.com" <u31540@uwe wrote in message news:6e7e1cf0ad1b7@uwe... I need to create a macro that will display a color in a cell depending on the condition of a different cell. For example if cell B1 has a 0 in it, A1 will turn red, otherwise it wont have a color. This needs to happen in about 4000 consecutive cells. Probably be easiest if it just happens when the workbook is opened. Thanks in advance for any help. -- Message posted via http://www.officekb.com |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colored Cell
I need to use six different colors...
Ron de Bruin wrote: Hi brownti You can use CF See http://www.contextures.com/xlCondFormat01.html I need to create a macro that will display a color in a cell depending on the condition of a different cell. For example if cell B1 has a 0 in it, A1 will turn red, otherwise it wont have a color. This needs to happen in about 4000 consecutive cells. Probably be easiest if it just happens when the workbook is opened. Thanks in advance for any help. -- Message posted via http://www.officekb.com |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colored Cell
You can use the change event in the sheet module then
Private Sub Worksheet_Change(ByVal Target As Range) Select Case Range("B1").Value Case 0: Range("A1:A4000").Interior.ColorIndex = 3 Case 1: Range("A1:A4000").Interior.ColorIndex = 5 Case 2: Range("A1:A4000").Interior.ColorIndex = 7 Case 3: Range("A1:A4000").Interior.ColorIndex = 9 Case 4: Range("A1:A4000").Interior.ColorIndex = 11 Case 5: Range("A1:A4000").Interior.ColorIndex = 13 Case Else: Range("A1:A4000").Interior.ColorIndex = xlColorIndexNone End Select End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "brownti via OfficeKB.com" <u31540@uwe wrote in message news:6e7e49cc53550@uwe... I need to use six different colors... Ron de Bruin wrote: Hi brownti You can use CF See http://www.contextures.com/xlCondFormat01.html I need to create a macro that will display a color in a cell depending on the condition of a different cell. For example if cell B1 has a 0 in it, A1 will turn red, otherwise it wont have a color. This needs to happen in about 4000 consecutive cells. Probably be easiest if it just happens when the workbook is opened. Thanks in advance for any help. -- Message posted via http://www.officekb.com |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colored Cell
Use this one, test first if B1 is the cell that is changed before you run the code
Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("B1"), Target) Is Nothing Then Select Case Range("B1").Value Case 0: Range("A1:A4000").Interior.ColorIndex = 3 Case 1: Range("A1:A4000").Interior.ColorIndex = 5 Case 2: Range("A1:A4000").Interior.ColorIndex = 7 Case 3: Range("A1:A4000").Interior.ColorIndex = 9 Case 4: Range("A1:A4000").Interior.ColorIndex = 11 Case 5: Range("A1:A4000").Interior.ColorIndex = 13 Case Else: Range("A1:A4000").Interior.ColorIndex = xlColorIndexNone End Select End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... You can use the change event in the sheet module then Private Sub Worksheet_Change(ByVal Target As Range) Select Case Range("B1").Value Case 0: Range("A1:A4000").Interior.ColorIndex = 3 Case 1: Range("A1:A4000").Interior.ColorIndex = 5 Case 2: Range("A1:A4000").Interior.ColorIndex = 7 Case 3: Range("A1:A4000").Interior.ColorIndex = 9 Case 4: Range("A1:A4000").Interior.ColorIndex = 11 Case 5: Range("A1:A4000").Interior.ColorIndex = 13 Case Else: Range("A1:A4000").Interior.ColorIndex = xlColorIndexNone End Select End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "brownti via OfficeKB.com" <u31540@uwe wrote in message news:6e7e49cc53550@uwe... I need to use six different colors... Ron de Bruin wrote: Hi brownti You can use CF See http://www.contextures.com/xlCondFormat01.html I need to create a macro that will display a color in a cell depending on the condition of a different cell. For example if cell B1 has a 0 in it, A1 will turn red, otherwise it wont have a color. This needs to happen in about 4000 consecutive cells. Probably be easiest if it just happens when the workbook is opened. Thanks in advance for any help. -- Message posted via http://www.officekb.com |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colored Cell
I dont think I was totally clear. It more like if B1 = 0 then A1 is red, if
b2 = 0 then A1 is red etc...through 4000 rows. i think i need a loop that checks each row individually. i dont really understand how to create loops though. Ron de Bruin wrote: You can use the change event in the sheet module then Private Sub Worksheet_Change(ByVal Target As Range) Select Case Range("B1").Value Case 0: Range("A1:A4000").Interior.ColorIndex = 3 Case 1: Range("A1:A4000").Interior.ColorIndex = 5 Case 2: Range("A1:A4000").Interior.ColorIndex = 7 Case 3: Range("A1:A4000").Interior.ColorIndex = 9 Case 4: Range("A1:A4000").Interior.ColorIndex = 11 Case 5: Range("A1:A4000").Interior.ColorIndex = 13 Case Else: Range("A1:A4000").Interior.ColorIndex = xlColorIndexNone End Select End Sub I need to use six different colors... [quoted text clipped - 10 lines] consecutive cells. Probably be easiest if it just happens when the workbook is opened. Thanks in advance for any help. -- Message posted via http://www.officekb.com |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colored Cell
Then we do it like this
I use the whole column here but you can change it to If Not Application.Intersect(Range("B1:B4000"), Target) Is Nothing Then Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("B:B"), Target) Is Nothing Then Select Case Target.Value Case 0: Target.Offset(0, -1).Interior.ColorIndex = 3 Case 1: Target.Offset(0, -1).Interior.ColorIndex = 5 Case 2: Target.Offset(0, -1).Interior.ColorIndex = 7 Case 3: Target.Offset(0, -1).Interior.ColorIndex = 9 Case 4: Target.Offset(0, -1).Interior.ColorIndex = 11 Case 5: Target.Offset(0, -1).Interior.ColorIndex = 13 Case Else: Target.Offset(0, -1).Interior.ColorIndex = xlColorIndexNone End Select End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "brownti via OfficeKB.com" <u31540@uwe wrote in message news:6e7e8545e1aec@uwe... I dont think I was totally clear. It more like if B1 = 0 then A1 is red, if b2 = 0 then A1 is red etc...through 4000 rows. i think i need a loop that checks each row individually. i dont really understand how to create loops though. Ron de Bruin wrote: You can use the change event in the sheet module then Private Sub Worksheet_Change(ByVal Target As Range) Select Case Range("B1").Value Case 0: Range("A1:A4000").Interior.ColorIndex = 3 Case 1: Range("A1:A4000").Interior.ColorIndex = 5 Case 2: Range("A1:A4000").Interior.ColorIndex = 7 Case 3: Range("A1:A4000").Interior.ColorIndex = 9 Case 4: Range("A1:A4000").Interior.ColorIndex = 11 Case 5: Range("A1:A4000").Interior.ColorIndex = 13 Case Else: Range("A1:A4000").Interior.ColorIndex = xlColorIndexNone End Select End Sub I need to use six different colors... [quoted text clipped - 10 lines] consecutive cells. Probably be easiest if it just happens when the workbook is opened. Thanks in advance for any help. -- Message posted via http://www.officekb.com |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colored Cell
That doesnt work for me. I dont understand. I feel like i a loop would be
easier, i just cant get it to work. It only has two different conditions it needs. If B1=0, A1 should be red, if B1 doesnt equal 0, go to next row. Ron de Bruin wrote: Then we do it like this I use the whole column here but you can change it to If Not Application.Intersect(Range("B1:B4000"), Target) Is Nothing Then Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("B:B"), Target) Is Nothing Then Select Case Target.Value Case 0: Target.Offset(0, -1).Interior.ColorIndex = 3 Case 1: Target.Offset(0, -1).Interior.ColorIndex = 5 Case 2: Target.Offset(0, -1).Interior.ColorIndex = 7 Case 3: Target.Offset(0, -1).Interior.ColorIndex = 9 Case 4: Target.Offset(0, -1).Interior.ColorIndex = 11 Case 5: Target.Offset(0, -1).Interior.ColorIndex = 13 Case Else: Target.Offset(0, -1).Interior.ColorIndex = xlColorIndexNone End Select End If End Sub I dont think I was totally clear. It more like if B1 = 0 then A1 is red, if b2 = 0 then A1 is red etc...through 4000 rows. i think i need a loop that [quoted text clipped - 20 lines] consecutive cells. Probably be easiest if it just happens when the workbook is opened. Thanks in advance for any help. -- Message posted via http://www.officekb.com |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colored Cell
Sorry that I not understand you (Dutch people are not so smart)
needs. If B1=0, A1 should be red, if B1 doesnt equal 0, go to next row. Go to the next row and then ? I need to use six different colors... Where do you use these colors then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "brownti via OfficeKB.com" <u31540@uwe wrote in message news:6e7ecb358ed1c@uwe... That doesnt work for me. I dont understand. I feel like i a loop would be easier, i just cant get it to work. It only has two different conditions it needs. If B1=0, A1 should be red, if B1 doesnt equal 0, go to next row. Ron de Bruin wrote: Then we do it like this I use the whole column here but you can change it to If Not Application.Intersect(Range("B1:B4000"), Target) Is Nothing Then Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("B:B"), Target) Is Nothing Then Select Case Target.Value Case 0: Target.Offset(0, -1).Interior.ColorIndex = 3 Case 1: Target.Offset(0, -1).Interior.ColorIndex = 5 Case 2: Target.Offset(0, -1).Interior.ColorIndex = 7 Case 3: Target.Offset(0, -1).Interior.ColorIndex = 9 Case 4: Target.Offset(0, -1).Interior.ColorIndex = 11 Case 5: Target.Offset(0, -1).Interior.ColorIndex = 13 Case Else: Target.Offset(0, -1).Interior.ColorIndex = xlColorIndexNone End Select End If End Sub I dont think I was totally clear. It more like if B1 = 0 then A1 is red, if b2 = 0 then A1 is red etc...through 4000 rows. i think i need a loop that [quoted text clipped - 20 lines] consecutive cells. Probably be easiest if it just happens when the workbook is opened. Thanks in advance for any help. -- Message posted via http://www.officekb.com |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colored Cell
Ok, i got it working, sort of.
Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim rnginput As Variant Set rnginput = Intersect(Target, Range("e:e")) If rnginput Is Nothing Then Exit Sub For Each rng In rnginput Select Case rng.Value Case 0: Target.Offset(0, -3).Interior.ColorIndex = 10 Case Else: Target.Offset(0, -3).Interior.ColorIndex = xlColorIndexNone End Select rng.Interior.ColorIndex = Num Next rng End Sub But now i need to do that 5 more times. Now i need if range "f:f" is 0, offset (0,-5).interior.colorindex=40 Ron de Bruin wrote: Sorry that I not understand you (Dutch people are not so smart) needs. If B1=0, A1 should be red, if B1 doesnt equal 0, go to next row. Go to the next row and then ? I need to use six different colors... Where do you use these colors then That doesnt work for me. I dont understand. I feel like i a loop would be easier, i just cant get it to work. It only has two different conditions it [quoted text clipped - 24 lines] consecutive cells. Probably be easiest if it just happens when the workbook is opened. Thanks in advance for any help. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200702/1 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colored Cell
something like this you mean
Private Sub Workbook_Open() Dim i As Integer For i = 1 To 4000 If Range("B" & i).Value = 0 Then Range("A" & i).Interior.ColorIndex = 3 Else Range("A" & i).Interior.ColorIndex = xlColorIndexNone ' whatever End If Next i End Sub regards iKKi<<< "brownti via OfficeKB.com" <u31540@uwe wrote in message news:6e7ecb358ed1c@uwe... That doesnt work for me. I dont understand. I feel like i a loop would be easier, i just cant get it to work. It only has two different conditions it needs. If B1=0, A1 should be red, if B1 doesnt equal 0, go to next row. Ron de Bruin wrote: Then we do it like this I use the whole column here but you can change it to If Not Application.Intersect(Range("B1:B4000"), Target) Is Nothing Then Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("B:B"), Target) Is Nothing Then Select Case Target.Value Case 0: Target.Offset(0, -1).Interior.ColorIndex = 3 Case 1: Target.Offset(0, -1).Interior.ColorIndex = 5 Case 2: Target.Offset(0, -1).Interior.ColorIndex = 7 Case 3: Target.Offset(0, -1).Interior.ColorIndex = 9 Case 4: Target.Offset(0, -1).Interior.ColorIndex = 11 Case 5: Target.Offset(0, -1).Interior.ColorIndex = 13 Case Else: Target.Offset(0, -1).Interior.ColorIndex = xlColorIndexNone End Select End If End Sub I dont think I was totally clear. It more like if B1 = 0 then A1 is red, if b2 = 0 then A1 is red etc...through 4000 rows. i think i need a loop that [quoted text clipped - 20 lines] consecutive cells. Probably be easiest if it just happens when the workbook is opened. Thanks in advance for any help. -- Message posted via http://www.officekb.com |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colored Cell
Sorry, i'll make it clear this time.
If G1=0 then A1 is red if it doesnt =0, no color if G2=0 then A2 is red if it doesnt =0, no color etc. If H1=0 then B1 is blue if it doesnt =0, no color If H2=0 then B2 is blue if it doesnt =0, no color etc. If I1=0 then C1 is green if it doesnt =0, no color If I2=0 then C2 is green if it doesnt =0, no color etc. Then there are three more columns that act in the same manner. All of these go on for about 4000 rows. I hope that is more clear. Ron de Bruin wrote: Sorry that I not understand you (Dutch people are not so smart) needs. If B1=0, A1 should be red, if B1 doesnt equal 0, go to next row. Go to the next row and then ? I need to use six different colors... Where do you use these colors then That doesnt work for me. I dont understand. I feel like i a loop would be easier, i just cant get it to work. It only has two different conditions it [quoted text clipped - 24 lines] consecutive cells. Probably be easiest if it just happens when the workbook is opened. Thanks in advance for any help. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200702/1 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colored Cell
No code needed
You can use CF with a formula then because you only use two conditions in each column -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "brownti via OfficeKB.com" <u31540@uwe wrote in message news:6e7f2554d6ce6@uwe... Sorry, i'll make it clear this time. If G1=0 then A1 is red if it doesnt =0, no color if G2=0 then A2 is red if it doesnt =0, no color etc. If H1=0 then B1 is blue if it doesnt =0, no color If H2=0 then B2 is blue if it doesnt =0, no color etc. If I1=0 then C1 is green if it doesnt =0, no color If I2=0 then C2 is green if it doesnt =0, no color etc. Then there are three more columns that act in the same manner. All of these go on for about 4000 rows. I hope that is more clear. Ron de Bruin wrote: Sorry that I not understand you (Dutch people are not so smart) needs. If B1=0, A1 should be red, if B1 doesnt equal 0, go to next row. Go to the next row and then ? I need to use six different colors... Where do you use these colors then That doesnt work for me. I dont understand. I feel like i a loop would be easier, i just cant get it to work. It only has two different conditions it [quoted text clipped - 24 lines] consecutive cells. Probably be easiest if it just happens when the workbook is opened. Thanks in advance for any help. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200702/1 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colored Cell
Good point. It works fine now....Sorry to waste your time
Ron de Bruin wrote: No code needed You can use CF with a formula then because you only use two conditions in each column Sorry, i'll make it clear this time. If G1=0 then A1 is red if it doesnt =0, no color [quoted text clipped - 25 lines] consecutive cells. Probably be easiest if it just happens when the workbook is opened. Thanks in advance for any help. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200702/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell is colored(red0....based on this how to change other cell val | Excel Discussion (Misc queries) | |||
| how to filter through a colored cell which has no value??? | Excel Discussion (Misc queries) | |||
| SUMIF cell is colored | Excel Discussion (Misc queries) | |||
| colored a cell | Excel Programming | |||
| Cell right next to colored cells is automatically colored on entering a value | Excel Programming | |||
Linear Mode
