Conditional Format based on cell value

[proteome]

I would like to change the cell background in a excel spreadsheet based on values within the individual cells. The best way I believe to do this is to use VBA since I have more than three conditions. I do not want to have to type the values into the cells again for the code to work which is what i currently have working I would like to have the worksheet automatically update. Like a macro running. Does anyone have any ideas.

Sub PrettyColors(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A2:E1334")) Is Nothing Then
        With Target
            Select Case .Value
                Case 0 To 10
                    icolor = 6
                Case 11 To 20
                    icolor = 7
                Case 21 To 30
                    icolor = 8
                Case 31 To 40
                    icolor = 9
                Case 41 To 50
                    icolor = 10
                Case 51 To 60
                    icolor = 11
                Case 61 To 70
                    icolor = 12
                Case 71 To 80
                    icolor = 13
                Case 81 To 90
                    icolor = 14
                Case 91 To 100
                    icolor = 15
                Case Else
                    'Whatever
            End Select
        End With
        Target.Interior.ColorIndex = icolor
    End If
End Sub

 

[SkipVought]

Hi,

try this

Sub AllPrettyColors()
    Dim icolor As Integer
    For Each r In Range("A2:E1334")
            With r
                Select Case .Value
                    Case 0 To 10
                        icolor = 6
                    Case 11 To 20
                        icolor = 7
                    Case 21 To 30
                        icolor = 8
                    Case 31 To 40
                        icolor = 9
                    Case 41 To 50
                        icolor = 10
                    Case 51 To 60
                        icolor = 11
                    Case 61 To 70
                        icolor = 12
                    Case 71 To 80
                        icolor = 13
                    Case 81 To 90
                        icolor = 14
                    Case 91 To 100
                        icolor = 15
                    Case Else
                        'Whatever
                End Select
                .Interior.ColorIndex = icolor
            End With
    Next
End Sub

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[proteome]

thanks skip works brilliantly

 

[mercwonder007]

The bottom one works but not like conditional formatting. When I enter a number say "4.0" I need the background to automatically turn we'll say "Blue" if I enter "3.0" I need it to turn "Yellow" and so on. Is there a way to do this automatically without having to run the macro?

 

[PHV]

Call the macro in the Worksheet_Change event procedure.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mercwonder007]

It is still not working properly. is there a way to have it function like conditional formatting. depending on what number is entered will determine what the background color should be?

 

[SkipVought]

Did you FIND the Worksheet_Change event procedure??? It will run EVERY TIME a value changes on the sheet.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[mercwonder007]

I am not able to find the "Worksheet_change event procedure. Where could i find it?

 

[SkipVought]

Right click the Sheet Tab of the Sheet that you want the Worksheet_Change event and select View Code.

This displays the Code Sheet for this Sheet Object.

In the Upper LH corner of the Code Sheet is the Objects Drop Down. Select Worksheet.

In the Upper RH corner of the Code Sheet is the Procedures Drop Down. Select Change.

You will see...

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Call you procedure from this procedure.



Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[mercwonder007]

I appreciate your help with this... I tried this and it automatically puts in a yellow background regardless of what I type in. I need to be able to have it blank until i type in a number then have it change to a specified color.

 

[SkipVought]

Which procedure are you calling from the Worksheet_Change event?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[mercwonder007]

As you can see, I a little new to VBA, and don't know my way around too well. I don't know what kind of procedure I am calling... I think it is a Sub Procedure?

 

[SkipVought]

You have at least TWO procedures

PrettyColors

AllPrettyColors

Are you calling one of these from the Worksheet_Change event? Which one?

The evidence is that the VALUE being tested is 0 to 10 because the cell interior is YELLOW.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[mercwonder007]

The name of the proceedure shouldn't matter, i can call it anything. I need to be able to have it as a white or clear background and then once i put in a value it changes the bacground color.

 

[SkipVought]

It makes a difference IF it's the one that is passing an ARGUMENT; [red]TARGET[/red]. THAT'S A PROBLEM!

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[mercwonder007]

I'm sorry, I don't quite follow, At the risk of sounding studpid, let's start from the beginning. I open a blank excel spreadsheet, i want to make column "M" the target. I go to type a number into column "M" and depending on that number will determine the color. What would be the VB script for that. Once again, I very much appreciate your help.

 

[SkipVought]

And I am asking you...

Which Procedure are you running in the Calculate Event???

It makes a difference! I can't answer you OTHER questions until I know WHAT CODE YOU ARE RUNNING!

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[mercwonder007]

I appreciate your patience. Let's assume I don't have any code to run... I'm starting from the very beginning.

 

[SkipVought]

If you are running from the Worksheet_Calculate event you have to evaluate ALL the cells in the range: hence the AllPrettyColors which does not use the Target argument.

So, in the Worksheet_Calculate event, turn off events, run the procedure, turn on events.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[mercwonder007]

This is what I have

Private Sub Worksheet_Calculate(ByVal Target As Range)

Dim icolor As Integer
For Each r In Range("A2:E1334")
With r
Select Case .Value
Case 0 To 10
icolor = 6
Case 11 To 20
icolor = 7
Case 21 To 30
icolor = 8
Case 31 To 40
icolor = 9
Case 41 To 50
icolor = 10
Case 51 To 60
icolor = 11
Case 61 To 70
icolor = 12
Case 71 To 80
icolor = 13
Case 81 To 90
icolor = 14
Case 91 To 100
icolor = 15
Case Else
'Whatever
End Select
.Interior.ColorIndex = icolor
End With
Next

End Sub

And still it doesn't work... Any ideas?