Simple Range help!

mrdees960

New Member
Joined
Jan 17, 2017
Messages
3
Pardon my ignorance as I am new to this wonderful world of scripts.

So far i have this,

Private Sub Worksheet_Change(ByVal Target As Range)
MyVal = Range("B2:B14").Text


With ActiveSheet.Tab
Select Case MyVal
Case ""
.Color = xlColorIndexNone
Case Else
.ColorIndex = 3
End Select
End With
End Sub

All i need to do here is when text is entered in B2 through B14 i need the tab to change from no color to green. With this soon as i put a value in any of the ones in range it changes. I am a Noob so please be gentle. :)
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Welcome to the Board!

I get a red color for index 3, but you can change that any way you like.
Here is the code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    Dim cell As Range
    
    Set myRange = Intersect(Target, Range("B2:B14"))
    
    If Not myRange Is Nothing Then
        For Each cell In myRange
            ActiveSheet.Tab.ColorIndex = 3
        Next cell
    End If

End Sub
 
Last edited:
Upvote 0
Sorry its index 4 for green. When I use the tab goes green soon as i place a value in one of the 13 cells. My ultimate goal is to have it go green once there is text (Anything for this spreadsheet) in all of the cells before the tab goes green. That make much sense?

Private Sub Worksheet_Change(ByVal Target As Range)


Dim myRange As Range
Dim cell As Range

Set myRange = Intersect(Target, Range("B2:B14"))

If Not myRange Is Nothing Then
For Each cell In myRange
ActiveSheet.Tab.ColorIndex = 4
Next cell
End If


End Sub
 
Upvote 0
OK, then try this:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    
    Set myRange = Range("B2:B14")
    If Not Intersect(Target, myRange) Is Nothing Then
        If Application.WorksheetFunction.CountBlank(myRange) = 0 Then
            ActiveSheet.Tab.ColorIndex = 4
        End If
    End If

End Sub
 
Upvote 0
You are welcome!
Glad I could help.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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