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

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

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

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

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

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

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

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

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

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

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

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

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

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