Conditional Formatting

[GoldDave]

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

[swatsp0p]

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.

[vba]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[/vba]

Good Luck

[Roger Govier]

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
>
>
>
>

[GoldDave]

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

[swatsp0p]

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.

[GoldDave]

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

[swatsp0p]

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.