Hi

Is there another way of putting conditional formatting on a worksheet
without using the 'Conditional Formatting' function.

I need to do conditional formatting to 5 or 6 levels however I am
limited by the conditional formatting function only allowing 3 e.g.

cell value is between 1 & 50 cell turns blue
cell value is between 51 & 100 cell turns green
cell value is between 101 & 250 cell turns yellow
cell value is between 251 & 500 cell turns orange
cell value is between 501 & 1000 cell turns magenta
cell value is between 1000 & 2500 cell turns Red

Currently i can only apply the first three rules...

...any ideas???

Thanks

Dave


--
GoldDave
------------------------------------------------------------------------
GoldDave's Profile: http://www.excelforum.com/member.php...o&userid=27750
View this thread: http://www.excelforum.com/showthread...hreadid=472580

Hi

take a look at John McGimpsey's solution for this
http://www.mcgimpsey.com/excel/conditional6.html

Regards

Roger Govier



GoldDave wrote:

Hi

Is there another way of putting conditional formatting on a worksheet
without using the 'Conditional Formatting' function.

I need to do conditional formatting to 5 or 6 levels however I am
limited by the conditional formatting function only allowing 3 e.g.

cell value is between 1 & 50 cell turns blue
cell value is between 51 & 100 cell turns green
cell value is between 101 & 250 cell turns yellow
cell value is between 251 & 500 cell turns orange
cell value is between 501 & 1000 cell turns magenta
cell value is between 1000 & 2500 cell turns Red

Currently i can only apply the first three rules...

..any ideas???

Thanks

Dave

Bruce

I thought that VBA would be used in this. The only problem is that I
can't ge tit to work.

My workbook consists of seven worksheets with the first six all
searching for data from the seventh e.g. a cell in the first sheet may
read =sheet7!H32.

The active area is A1:DM112 - is it this range I insert into the
following

If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then

If it is I have tried it all without success - I did notice that Me in
Me.Range above is blue in your code whereas in mine its black - will
this have anything to do with it?

Thanks for the help

Dave


--
GoldDave
------------------------------------------------------------------------
GoldDave's Profile: http://www.excelforum.com/member.php...o&userid=27750
View this thread: http://www.excelforum.com/showthread...hreadid=472580

The code listed in my original post was to change the cell that
contained the value.
Your original post made it seem that was your goal...: "cell value is
between 1 & 50 cell turns blue"

Let me understand, do you want to change the color of one cell (which
may be blank) based on the contents of another cell (that will be on
another sheet)?



More info, please.


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=472580

Thanks for replying...

...the workbook consists of 7 worksheets. One worksheet contains all of
the data with the other 6 performing a 3d reference to it i.e.

worksheets are named;

Total
CUN
CSI
CNI
IRM
DUP
Data

One cell in the Total worksheet could have a formula stating =Data!H32,
so infact there are no values in each cell.

As a work round I did copy the 'Total' worksheet and paste special
values only into another sheet using the VB code but it didn't work.
This is how mine looks;



Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
' adjust range below to meet your needs
If Not Intersect(Target, Me.Range("A1:DM114")) Is Nothing Then
With Target
Select Case UCase(.Value)
'Case Is 2500: .Interior.ColorIndex = ? do you have
values 2500?
Case Is 1000: .Interior.ColorIndex = 3
Case Is 500: .Interior.ColorIndex = 13
Case Is 250: .Interior.ColorIndex = 46
Case Is 100: .Interior.ColorIndex = 6
Case Is 50: .Interior.ColorIndex = 10
Case Is 0: .Interior.ColorIndex = 5
'etc.
End Select
End With
End Ifws_exit:
Application.EnableEvents = True
End Sub

I've tried to match the colours as best I can!?

Thanks and regards

Dave


--
GoldDave
------------------------------------------------------------------------
GoldDave's Profile: http://www.excelforum.com/member.php...o&userid=27750
View this thread: http://www.excelforum.com/showthread...hreadid=472580

Assuming you have the code in the TOTAL sheet as instructed, on the
TOTAL sheet select the cell with the formula: =Data!H32 and press F2
then ENTER. (this will trigger the worksheet_change for this cell).

What happens to the cell fill color?

If Data!H32 contains the value 1, the cell should turn Blue.

Note this code is only triggered when a cell within the specified range
(Target, Me.Range("A1:DM114") in your example) changes and then only on
THAT cell.

I'm guessing this is not going to meet your needs as you would need to
manually recalculate the range, cell by cell.

Hopefully, someone can come up with a better solution for you.

Sorry.


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=472580

you can use VBA code pasted into the "sheet module" of the sheet -
right mouse click on the sheet tab and choose view / code. The VBA
project window opens, on the right you see some white space - copy &
paste the code in there.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
' adjust range below to meet your needs
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
Select Case UCase(.Value)
'Case Is 2500: .Interior.ColorIndex = ? do you have
values 2500?
Case Is 1000: .Interior.ColorIndex = 3
Case Is 500: .Interior.ColorIndex = 13
Case Is 250: .Interior.ColorIndex = 46
Case Is 100: .Interior.ColorIndex = 6
Case Is 50: .Interior.ColorIndex = 10
Case Is 0: .Interior.ColorIndex = 5
'etc.
End Select

End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Good Luck


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=472580