Solved: error

[satish gubbi]

I am getting compilation error for the below code. Please help

and also I need to run this code for all the worksheets available in a workbook

Please help

Dim rng As Range
Dim cell As Range
Set rng = Intersect(Range("A4,A5"), ActiveSheet.UsedRange)

For Each cell In rng
If (cell.Value) = "Satish" _
Or (cell.Value) = "Anusha" Then

ActiveWorkbook.Sheets("Sheet1").Tab.ColorIndex = vbRed
Else
ActiveWorkbook.Sheets("Sheet1").Tab.ColorIndex = xlNone
End If

Next cell
On Error Resume Next

[Krishna Kumar]

Hi,

Welcome to board !!

Try this

Sub kTest()

Dim i As Long
Dim c As Long
Dim j As Long

c = ThisWorkbook.Worksheets.Count

For i = 1 To c
j = Evaluate("sum(countif('" & Worksheets(i).Name & "'!a4:a5" & ",{""Satish"",""Anusha""}))")
If j Then
Worksheets(i).Tab.Color = vbRed
Else
Worksheets(i).Tab.Color = xlNone
End If
Next

End Sub

[mohanvijay]

try this

Dim WS_Sub As Worksheet
Dim Rng As Range
Dim Hld_Cri(1) As String
Dim i As Integer
Hld_Cri(0) = "Satish"
Hld_Cri(1) = "Anusha"
For Each WS_Sub In ActiveWorkbook.Worksheets

For i = 0 To 1

Set Rng = WS_Sub.UsedRange.Find(What:=Hld_Cri(i), lookat:=xlWhole)
If Not Rng Is Nothing Then Exit For

Next i

If Rng Is Nothing Then

WS_Sub.Tab.ColorIndex = xlNone
Else

WS_Sub.Tab.Color = vbRed
End If

Set Rng = Nothing
Next
Set WS_Sub = Nothing

[mdmackillop]

Your compile error was ColorIndex = vbRed.

A small change to your original code logic; reset tab colours before testing values


For Each sh In Sheets
Set rng = sh.Range("A4,A5")
sh.Tab.ColorIndex = xlNone
For Each cell In rng
If (cell.Value) = "Satish" _
Or (cell.Value) = "Anusha" Then
sh.Tab.ColorIndex = 3
End If
Next cell
Next

[satish gubbi]

Hi mdmackillop

THIS CODE WORKED !!!

I heartfelt expression, thank you very much for your help
Regards,
Satish

[satish gubbi]

Your compile error was ColorIndex = vbRed.

A small change to your original code logic; reset tab colours before testing values


For Each sh In Sheets
Set rng = sh.Range("A4,A5")
sh.Tab.ColorIndex = xlNone
For Each cell In rng
If (cell.Value) = "Satish" _
Or (cell.Value) = "Anusha" Then
sh.Tab.ColorIndex = 3
End If
Next cell
Next

Hi mdmackillop

Can you please help me in getting the below syntax added to the code, so that it can search words irrespective of format

(LookIn:=xlValues, LookAt:=False, MatchCase:=False)

[mdmackillop]

Option Explicit
Option Compare Text

Sub ColTab()
Dim sh As Worksheet
Dim Rng As Range
Dim Cell As Range

For Each sh In Sheets
Set Rng = sh.Range("A4,A5")
sh.Tab.ColorIndex = xlNone
For Each Cell In Rng
If (Cell.Value) Like "*satish*" _
Or (Cell.Value) Like "*anusha*" Then
sh.Tab.ColorIndex = 3
End If
Next Cell
Next
End Sub