Thread: problem coloring sheet tab based on a condition

hey everyone

i'm using this code below to color sheet tab if string "9.9999" found in column "A" on an excel sheet.

Code:

Dim ws As Worksheet
Dim wb As Workbook
FoulTime = "99999"
Dim rng As Range

Set wb = ThisWorkbook
For Each ws In wb.Worksheets
          
         ws.Columns("a:a").AutoFit
         
    Set rng = ws.UsedRange.Find(FoulTime)
        
        If rng Is Nothing Then
        ws.Tab.ColorIndex = 4
           
        Else
        ws.Tab.ColorIndex = 3
       
              End If
             
    Next ws

recap: if "9.9999"found Color Tab = Red, if not Green.
The problem is what if wanted the Color to become red if both strings "9.9999" OR/AND "0.0000" found?

how do i do ?
thanks in advance
regards

Hello mehdoush,

You need to add another IF statement. This also checks if the cell is an empty string. The empty string is often considered to be a zero value.

Code:

Dim ws As Worksheet
Dim wb As Workbook
FoulTime = "99999"
Dim rng As Range

Set wb = ThisWorkbook
For Each ws In wb.Worksheets
          
         ws.Columns("a:a").AutoFit
         
    Set rng = ws.UsedRange.Find(FoulTime)
        
        If rng Is Nothing Then

          ws.Tab.ColorIndex = 4
 
        Else

          If Rng.Value = "" Or Rng.Value = "0.0000" Then
            ws.Tab.ColorIndex = 3
          End If

        End If
             
    Next ws

Leith, sorry it didn't work properly

i already defined a range

Code:

FoulTime = "99999"
Dim rng As Range
Set rng = ws.UsedRange.Find(FoulTime)

can i add this string "0.0000" to the range ?

regards