I have a need for conditional formatting based on greater than 6 conditions
and have found VBA programming on http://www.ozgrid.com/V (*thank you very
much!*) and modified it slightly. It works perfectly for the data manually
entered in the cells in the Target Range. However, I am trying to use the
same conditional formatting on a calculated cell in the Target Range and I
can't get it to work the way I want. The calculated cell will turn the
color defined in the Case statement the first time it is calculated but then
it stays that color and will not reflect the defined color for the the Case
statement.

The data in the Target Range are percentages and here is the modified code
(row 16 cells are =Average(B3:B16), etc. and will not change color per the
case statement)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("B3:P16")) Is Nothing Then
Select Case Target
Case 0 To 0.0001
iscolor = 2
Case 0.0001 To 0.2499
icolor = 3
Case 0.25 To 0.4999
icolor = 7
Case 0.5 To 0.6499
icolor = 6
Case 0.65 To 0.7499
icolor = 8
Case 0.75 To 1#
icolor = 4
Case Else
icolor = 2

End Select

Target.Interior.ColorIndex = icolor
End If

End Sub

Thanks, Tonly

Try the calculate event

Private Sub Worksheet_Calculate()
Dim Target As Range
Dim iColor As Integer

For Each Target In Me.Range("B3:P16")
Select Case Target
Case 0 To 0.0001
iscolor = 2
Case 0.0001 To 0.2499
iColor = 3
Case 0.25 To 0.4999
iColor = 7
Case 0.5 To 0.6499
iColor = 6
Case 0.65 To 0.7499
iColor = 8
Case 0.75 To 1#
iColor = 4
Case Else
iColor = 2

End Select

Target.Interior.ColorIndex = iColor
End If

End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Thyristor" <tknorris at plateautel dot net> wrote in message
news:ecUgZg$(E-Mail Removed)...
>I have a need for conditional formatting based on greater than 6 conditions
>and have found VBA programming on http://www.ozgrid.com/V (*thank you very
>much!*) and modified it slightly. It works perfectly for the data manually
>entered in the cells in the Target Range. However, I am trying to use the
>same conditional formatting on a calculated cell in the Target Range and I
>can't get it to work the way I want. The calculated cell will turn the
>color defined in the Case statement the first time it is calculated but
>then it stays that color and will not reflect the defined color for the the
>Case statement.
>
> The data in the Target Range are percentages and here is the modified code
> (row 16 cells are =Average(B3:B16), etc. and will not change color per
> the case statement)
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim icolor As Integer
>
> If Not Intersect(Target, Range("B3:P16")) Is Nothing Then
> Select Case Target
> Case 0 To 0.0001
> iscolor = 2
> Case 0.0001 To 0.2499
> icolor = 3
> Case 0.25 To 0.4999
> icolor = 7
> Case 0.5 To 0.6499
> icolor = 6
> Case 0.65 To 0.7499
> icolor = 8
> Case 0.75 To 1#
> icolor = 4
> Case Else
> icolor = 2
>
> End Select
>
> Target.Interior.ColorIndex = icolor
> End If
>
> End Sub
>
> Thanks, Tonly
>
>

Hi Tonly,
Try it as a Worksheet_Calculate event procedure...

Private Sub Worksheet_Calculate()
Dim icolor As Integer
Dim rngCell

For Each rngCell In Range("B3:P16")
Select Case rngCell.Value
Case 0 To 0.0001
icolor = 2
Case 0.0001 To 0.2499
icolor = 3
Case 0.25 To 0.4999
icolor = 7
Case 0.5 To 0.6499
icolor = 6
Case 0.65 To 0.7499
icolor = 8
Case 0.75 To 1#
icolor = 4
Case Else
icolor = 2

End Select

rngCell.Interior.ColorIndex = icolor
Next rngCell
End Sub

Also, you're 1st icolor, for Case 0 To 0.0001, had a typo (iscolor),
and did you really mean =AVERAGE(B3:B16) in row 16 cells, which would
create a circular reference?

Ken Johnson

Hi Thyristor,

It may be a coincidence, but looking at your code, your first case (0
to .0001) sets the value of the variable 'iscolor' where the rest are
using tha variable 'icolor' - this may fix your problem.

I didn't look any further than that.

Cheers,
Ivan.

On Jan 10, 1:11 am, "Thyristor" <tknorris at plateautel dot net>
wrote:
> I have a need for conditional formatting based on greater than 6 conditions
> and have found VBA programming onhttp://www.ozgrid.com/V(*thank you very
> much!*) and modified it slightly. It works perfectly for the data manually
> entered in the cells in the Target Range. However, I am trying to use the
> same conditional formatting on a calculated cell in the Target Range and I
> can't get it to work the way I want. The calculated cell will turn the
> color defined in the Case statement the first time it is calculated but then
> it stays that color and will not reflect the defined color for the the Case
> statement.
>
> The data in the Target Range are percentages and here is the modified code
> (row 16 cells are =Average(B3:B16), etc. and will not change color per the
> case statement)
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim icolor As Integer
>
> If Not Intersect(Target, Range("B3:P16")) Is Nothing Then
> Select Case Target
> Case 0 To 0.0001
> iscolor = 2
> Case 0.0001 To 0.2499
> icolor = 3
> Case 0.25 To 0.4999
> icolor = 7
> Case 0.5 To 0.6499
> icolor = 6
> Case 0.65 To 0.7499
> icolor = 8
> Case 0.75 To 1#
> icolor = 4
> Case Else
> icolor = 2
>
> End Select
>
> Target.Interior.ColorIndex = icolor
> End If
>
> End Sub
>
> Thanks, Tonly

Thanks Bob, that did the job!

"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Try the calculate event
>
> Private Sub Worksheet_Calculate()
> Dim Target As Range
> Dim iColor As Integer
>
> For Each Target In Me.Range("B3:P16")
> Select Case Target
> Case 0 To 0.0001
> iscolor = 2
> Case 0.0001 To 0.2499
> iColor = 3
> Case 0.25 To 0.4999
> iColor = 7
> Case 0.5 To 0.6499
> iColor = 6
> Case 0.65 To 0.7499
> iColor = 8
> Case 0.75 To 1#
> iColor = 4
> Case Else
> iColor = 2
>
> End Select
>
> Target.Interior.ColorIndex = iColor
> End If
>
> End Sub
>
> --
> ---
> HTH
>
> Bob
>
> (change the xxxx to gmail if mailing direct)
>
>
> "Thyristor" <tknorris at plateautel dot net> wrote in message
> news:ecUgZg$(E-Mail Removed)...
>>I have a need for conditional formatting based on greater than 6
>>conditions and have found VBA programming on http://www.ozgrid.com/V
>>(*thank you very much!*) and modified it slightly. It works perfectly for
>>the data manually entered in the cells in the Target Range. However, I am
>>trying to use the same conditional formatting on a calculated cell in the
>>Target Range and I can't get it to work the way I want. The calculated
>>cell will turn the color defined in the Case statement the first time it
>>is calculated but then it stays that color and will not reflect the
>>defined color for the the Case statement.
>>
>> The data in the Target Range are percentages and here is the modified
>> code (row 16 cells are =Average(B3:B16), etc. and will not change color
>> per the case statement)
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> Dim icolor As Integer
>>
>> If Not Intersect(Target, Range("B3:P16")) Is Nothing Then
>> Select Case Target
>> Case 0 To 0.0001
>> iscolor = 2
>> Case 0.0001 To 0.2499
>> icolor = 3
>> Case 0.25 To 0.4999
>> icolor = 7
>> Case 0.5 To 0.6499
>> icolor = 6
>> Case 0.65 To 0.7499
>> icolor = 8
>> Case 0.75 To 1#
>> icolor = 4
>> Case Else
>> icolor = 2
>>
>> End Select
>>
>> Target.Interior.ColorIndex = icolor
>> End If
>>
>> End Sub
>>
>> Thanks, Tonly
>>
>>
>
>