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. :)
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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:

mrdees960

New Member
Joined
Jan 17, 2017
Messages
3
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
You are welcome!
Glad I could help.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,574
Messages
5,596,942
Members
414,113
Latest member
mboo86

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
Top