Help! Beginner's Excel Macro Problem

[blaqk]

Hey.
So I am using this code as a macro in excel to colour the mentioned cell when the value of b9 is in the appropriate level range.
However it is not colouring the proper cells, it seems to be colouring only B11
So the levels are
0-18.49 color cell B11
18.5 - 24.9 color cell B12
25 - 29.9 color cell B13
30- 100 color cell B14
here is the code

Sub AssignmentQuestion4()

If B9 < 18.49 Then
Range("B11").Select
With Selection.Interior
.Color = 655

End With
End If

If 18.5 < B9 And B9 < 24.9 Then
Range("B12").Select
With Selection.Interior
.Color = 655

End With
End If

If Formula = 25 < B9 And Formula < 29.9 Then
Range("B13").Select
With Selection.Interior
.Color = 655

End With
End If
If Formula = B9 > 30 Then
Range("B14").Select
With Selection.Interior
.Color = 655

End With
End If

End Sub

I apologize if this is not allowed, I am new to the boards and new to VB, and would very much appreciate any help.

 

[kieran]

Try this

Sub AssignmentQuestion4()

With ActiveSheet
    Select Case .Range("B9").Value
    
    Case 0 To 18.49
        .Range("B11").Interior.Color = 655
    
    Case 18.5 To 24.9
        .Range("B12").Interior.Color = 655
    
    Case 25 To 29.9
        .Range("B13").Interior.Color = 655
    
    Case Else  ' > 30
        .Range("B14").Interior.Color = 655
    
    End Select
End With


End Sub

 

[Misca]

Try:

Dim V As Double
Dim Rng As Range

V = Range("B9").Value

Set Rng = Range("B11:B14")
Rng.Interior.Pattern = xlNone   'no colors in B11:B14

Select Case V
    Case Is <= 18.49        'less than or equal to 18.49
        Set Rng = Range("B11")
    Case 18.49 To 24.9      'greater than 18.49 but less than equal to 24.9
        Set Rng = Range("B12")
    Case 24.9 To 29.9
        Set Rng = Range("B13")
    Case 29.9 To 100
        Set Rng = Range("B14")
    Case Else               ' if none of the abowe is true
        Set Rng = Range("B11:B14")
End Select

Rng.Interior.Color = 655

 

[blaqk]

Try this

Sub AssignmentQuestion4()

With ActiveSheet
    Select Case .Range("B9").Value
    
    Case 0 To 18.49
        .Range("B11").Interior.Color = 655
    
    Case 18.5 To 24.9
        .Range("B12").Interior.Color = 655
    
    Case 25 To 29.9
        .Range("B13").Interior.Color = 655
    
    Case Else  ' > 30
        .Range("B14").Interior.Color = 655
    
    End Select
End With


End Sub

I honestly love you thanks so much. That was exactly what I needed. As I said I don't know much about VBA but that was perfect.
Thanks again.

 

[blaqk]

Thanks for the help too Misca :D

I guess this thread an be closed. Thanks for the help all!