VBA Conditional formatting

[Moonyshine99]

Hi all,

I'm working on a spreadsheet that colours cells according to the cells value (as in conditional formatting). I've used VBA to forfill three of the four criteria necessary as follows.

Private Sub Worksheet_Calculate()

Set myrange = Range("c13:c32")


Dim iColor As Integer
Dim cell As Range


For Each cell In myrange

Select Case cell.Value

Case Is < 85 'Red
iColor = 3
Case 85 To 90 'Amber
iColor = 44
Case Is > 90 'Green
iColor = 10

End Select


cell.Interior.ColorIndex = iColor

Next

End Sub



That was simple enough except I'd like one further colour, and the input comes from a different sheet in the same workbook (luckily with the same cell reference) The formatting criteria would be case <20 producing a cell colorindex of 20. Would it be possible to modify the vba script to accomodate this fourth conditional format.

Kind regards,

Moony

[JBeaucaire]

Hi MoonyShine, welcome to the forum. Be sure to read through the Forum Rules you agreed to already so you can use and follow them effectively. For instance, you'll need to edit that post above and put code tags around that code you used.

Like this:

Please Login or Register  to view this content.

=======
Also, what is the other sheet you want to test for this alternate value?

[Moonyshine99]

Thanks JBeaucaire.

Thank you for the advice.

Please Login or Register  to view this content.

The other sheet I'd like to test for this value is called ProviderTableTots


Kind regards,

Moony