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

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
>

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

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
>

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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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
>>

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

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
>

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

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
>

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