5 Conditional formatting

Microsoft Excel 2003 (full product)
June 4, 2010 at 06:52:00
Specs: Windows XP
Hi, I have a calculated field that I need to have five colors coded into the result.

Excel 2003 has 3 options for confional formatting. I found a way to change the cells using the worksheet change but that doesnt help because its a calulated field that needs to be done automically. Can anyone help? please.

Can I put this in a macro button or something?


Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

If Not Intersect(Target, Range("G48:G65")) Is Nothing Then

Select Case Target

Case 10 To 25

icolor = 6

Case Is > 25

icolor = 3

Case -10 To -25

icolor = 11

Case Is < -25

icolor = 9


Case Else

'Whatever

End Select


Target.Interior.ColorIndex = icolor

End If


End Sub


See More: 5 Conditional formatting

Report •


#1
June 4, 2010 at 07:24:03
Hi,

If the color of the cell is dependent on a formula, identify which cell or cells are precedents of the cell with the formula.

Make your macro respond to changes in those precedent cells.

For example if the formula in cell X1 is =A1*B2 then you test the change event with:
If Target.Address = "$A$1" Or Target.Address = "$B$2" Then
(Note the $ signs in the address string)

Then the macro uses the calculated value in X1 to select the color - using the Select Case
Select Case ActiveSheet.Range("X1").Value

Regards


Report •

#2
June 4, 2010 at 07:38:14
Thanks very much for your help, the only problem is the above code only works in a worksheet change (view code) and not in a macro module. so the cell only changes when I change a number and I have tried to assign it to a macro without success. Please advise.

If i had a button that could do this in macro will be great, or even automatically.


Report •

#3
June 4, 2010 at 08:08:14
Hi,

Am I right in thinking that you want the color of a cell to change dependent on it's value, and that the cell contains a formula.

If so then the formula must be dependent on other cells.

The change event triggers when any cell on the worksheet is changed, so just set the on change event to respond to only those cells that cause your formula to change value.

Please post the formula and the address of the cell it is in.

Regards


Report •

Related Solutions

#4
June 4, 2010 at 08:25:25
=(F48+F47+F46)/3


so if result

is between 10 and 25 red
if > 25 blue

if between -10 and -25 yellow

if > -25 green

Work sheet code is below but only works if I type it in again.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

If Not Intersect(Target, Range("G48:G65")) Is Nothing Then

Select Case Target

Case 10 To 25

icolor = 6

Case Is > 25

icolor = 3

Case -10 To -25

icolor = 11

Case Is < -25

icolor = 9


Case Else

'Whatever

End Select


Target.Interior.ColorIndex = icolor

End If


End Sub


Thanks


Report •

#5
June 4, 2010 at 13:05:35
Hi,

You didn't say which cell contained your formula. I have used cell G4 (G4 contains =(F48+F47+F46)/3). As a result you will have to change G4 in the code (it appears twice in the code and 3 other times in comments).

Select the name tab of the worksheet containing cell G4 and cells F46:F48.
In the visual basic window that opens enter this code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'stop changes made by this code from re-triggering it
Application.EnableEvents = False

On Error GoTo ErrHnd

Dim rngIsect As Range
Dim intColor As Integer

'respond to changes in cells F46, F47 & F48 only
Set rngIsect = Intersect(Target, ActiveSheet.Range("F46:F48"))
'test if changed cell is in the required range
If Not rngIsect Is Nothing Then
    'select color based on value in cell G4
    'change cell as required
    'G4 contains =(F48+F47+F46)/3
    Select Case ActiveSheet.Range("G4").Value
        Case 10 To 25
            intColor = 6
        Case Is > 25
            intColor = 3
        Case -10 To -25
            intColor = 11
        Case Is < -25
            intColor = 9
        Case Else
            intColor = xlColorIndexNone
    End Select
    'change cell G4's color
    ActiveSheet.Range("G4").Interior.ColorIndex = intColor
End If

're-enable events
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True
End Sub

Any changes to cells F46, F47 or F48 will result in the color of cell G4 changing based on the value in that cell resulting from the formula =(F48+F47+F46)/3

(I didn't test the logic of your color selections)

Regards


Report •

#6
June 5, 2010 at 11:58:56
Hi, thanks for all your help I really appreciate it. The issue is I have about 15 columns and 18 rows of data that need to change color.

I have a total of 80 columns and every 4 will be doing the color code. so I will need to apply the code to all the columns. the code cant be specific.

Can it be more flexible please?

Sample

Male Female difference
10 15
15 25 28.33333333
10 45 25
2 5 16.66666667
1.666666667

the difference column will be the calculated field changing colors.


Report •

#7
June 5, 2010 at 13:25:34
Reply,

1.
How does the formula you posted (=(F48+F47+F46)/3)) relate to the difference column will be the calculated field changing colors that you refer to in response 6.

2.
The code can be made to respond to any change event on the worksheet - i.e. it will be very general.
But the code still has to know specifically which cells have to be tested for values and have their colors updated each time. Can you be specific about which cells will have color applied based on their values.

3.
You say The issue is I have about 15 columns and 18 rows of data that need to change color.
Do you really mean that you have 270 cells that change color (15 * 18)

4.
I don't understand your 'sample'
If you post data between the <pre> and </pre> tags that you can find above the reply box it would help, as the data can be lined up. Including column and row letters/numbers also helps.

Regards


Report •

#8
June 5, 2010 at 14:10:05
Ok,

1) The formula is in the difference column which will be where the colors will show. so I have a difference column 15 times.

Color changes if the result of the formula is
between 10 to 25
>25
-10 to -25
> -25


Columns are
A, C, F, i, M, O, Q, U, V, X, AA, AC, AF, AI, AO
(cells 48 to 65 for each row)

Hope this is clear, dont have access to my system at the moment. Does this help?


Report •

#9
June 5, 2010 at 15:22:21
Hi,

This macro responds to any change event on the worksheet.
It then tests the values in all 270 specified cells and changes their color according to their value.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'stop changes made by this code from re-triggering it
Application.EnableEvents = False

On Error GoTo ErrHnd

Dim rngCell As Range
Dim intC As Integer
Dim intColor As Integer

'respond to changes in any cell on this worksheet

'test values in 270 cells in columns:
'A, C, F, I, M, O, Q, U, V, X, AA, AC, AF, AI & AO
'and rows 48 to 65

'loop through all cells in range A48 to AO65
For Each rngCell In ActiveSheet.Range("A48:AO65")
    intC = rngCell.Column
    'test if cell is in one of the specified columns
    If intC = 1 Or intC = 3 Or intC = 6 Or intC = 9 _
        Or intC = 13 Or intC = 15 Or intC = 17 Or intC = 21 _
        Or intC = 22 Or intC = 24 Or intC = 27 Or intC = 29 _
        Or intC = 32 Or intC = 35 Or intC = 41 Then
        'if so - test cell value
        Select Case rngCell.Value
            Case 10 To 25
                intColor = 6
            Case Is > 25
                intColor = 3
            Case -10 To -25
                intColor = 11
            Case Is < -25
                intColor = 9
            Case Else
                intColor = xlColorIndexNone
        End Select
        'and change cell's color
        rngCell.Interior.ColorIndex = intColor
    End If
Next rngCell
're-enable events
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True
End Sub

Regards


Report •

#10
June 5, 2010 at 15:34:06
Okay, thanks very much for your help. Really really appreciate!!!!!

Thanks


Report •

#11
June 7, 2010 at 01:13:33
Hi, just tried the code on my worksheet and nothing is happening. I have pasted it into the view code and also a module and its not chnaging color. Please advise.

Report •

#12
June 7, 2010 at 09:15:27
Hi,

The macro is a change event macro, meaning that it runs when a change occurs in a cell on the worksheet it is attached to.

Nothing will ever happen if it is in a Module.

Select the worksheet containing the 270 values.
Right-click on the name tab at the bottom and select 'View code'
In the Visual basic window that opens make sure that the window is empty - delete any code present.
Then copy the whole of the code I submitted, from Option Explicit to End Sub (inclusive) and paste it into the Visual Basic window.

Use Alt+f11 to go back to the main Excel window.

Click on any empty cell in the worksheet and enter something and click enter. The 270 cells should change color.

If nothing happens, it is possible that Events are disabled.
In The Visual Basic window, from the Menu bar, select 'View' and 'Immediate Window'. In the Immediate window that opens, scroll down to the bottom of the list and enter:
Application.EnableEvents = True and click Enter, and test again.

If it still does nothing, add a Break Point to the code (at the For Each rngCell ... line) and see if it stops at that point.

Regards


Report •


Ask Question