Help! Beginner's Excel Macro Problem

blaqk

New Member
Joined
Jan 19, 2010
Messages
3
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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Try this

Code:
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
 
Upvote 0
Try:

Code:
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
 
Upvote 0
Try this

Code:
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.
 
Upvote 0
Thanks for the help too Misca :D

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

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top