Change from ActiveSheet to ActiveWorkbook

[bsquad]

I am having some trouble changing the lookup range from the ActiveSheet to the ActiveWorkbook. So instead of the Sub process looking on the active sheet, I am trying to have it look at each different sheet in the workbook. The error I am getting is Rumtime error 1004, No Cells were Found

I am hoping it is just a syntax thing

Sub FormulaSource2()
Dim rng As Range
Dim Cell As Range
Dim ws As Worksheet
Dim wb As Workbook

For Each ws In ActiveWorkbook.Worksheets
Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
For Each Cell In rng
If CBool(InStr(1, rng.Formula, "'\\File-na", vbTextCompare)) Then
rng.Font.Bold = True
Else
End If
Next Cell
Next ws
End Sub

 

[Domenic]

Try...

[COLOR=darkblue]Sub[/COLOR] FormulaSource2()
    [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] Cell [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] ActiveWorkbook.Worksheets
        [COLOR=darkblue]Set[/COLOR] rng = ws.Cells.SpecialCells(xlCellTypeFormulas)
        [COLOR=darkblue]If[/COLOR] Err = 0 [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Cell [COLOR=darkblue]In[/COLOR] rng
                [COLOR=darkblue]If[/COLOR] InStr(1, Cell.Formula, "'\\File-na", vbTextCompare) Then
                    Cell.Font.Bold = [COLOR=darkblue]True[/COLOR]
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]Next[/COLOR] Cell
        [COLOR=darkblue]Else[/COLOR]
            Err.Clear
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] ws
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[bsquad]

Thanks so much!!

btw is there a 'SOLVED' button I am not seeing for these posts?

 

[Domenic]

Thanks so much!!

You're very welcome!

btw is there a 'SOLVED' button I am not seeing for these posts?

No, there's no SOLVED button. So there's no need to do anything.

Cheers!

 

[bsquad]

You're very welcome!



No, there's no SOLVED button. So there's no need to do anything.

Cheers!

Domenic I am wondering if you could assist with one more; I am trying to use similar syntax and structure you had given me.
I am basically trying to light up values that are hardcoded.

Criteria:
Has a bottom border (any size or color)
Has a cell fill color = white
Has NoFormula but is not blank

I have tried a couple different variants,

Sub Hardcode()
Dim rng As Range
Dim cell As Range
Dim ws As Worksheet
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
Set rng = ws.Cells.Borders(xlEdgeBottom).LineStyle <> xlNone
If Err = 0 Then
For Each cell In rng
If rng.HasFormula = False And rng.Cells.Interior.Color = RGB(255, 255, 255) Then
cell.Font.Color = RGB(192, 0, 255)
End If
Next cell
Else
Err.Clear
End If
Next ws
On Error GoTo 0
End Sub




Sub Hardcode()
Dim rng As Range
Dim cell As Range
Dim ws As Worksheet
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
Set rng = ws.Cells.Borders(xlEdgeBottom).LineStyle <> xlNone
If Err = 0 Then
For Each cell In rng
If rng.HasFormula = False Then
If rng.Interior.Color = RGB(255, 255, 255) Then
cell.Font.Color = RGB(192, 0, 255)
End If
End If
Next cell
Else
Err.Clear
End If
Next ws
On Error GoTo 0
End Sub


Thanks so much!

 

[bsquad]

sorry, with tags-
[/CODE]

[COLOR=#574123]
Sub Hardcode()[/COLOR]
[COLOR=#574123]Dim rng As Range[/COLOR]
[COLOR=#574123]Dim cell As Range[/COLOR]
[COLOR=#574123]Dim ws As Worksheet[/COLOR]
[COLOR=#574123]On Error Resume Next[/COLOR]
[COLOR=#574123]For Each ws In ActiveWorkbook.Worksheets[/COLOR]
[COLOR=#574123]Set rng = ws.Cells.Borders(xlEdgeBottom).LineStyle <> xlNone[/COLOR]
[COLOR=#574123]If Err = 0 Then[/COLOR]
[COLOR=#574123]For Each cell In rng[/COLOR]
[COLOR=#574123]If rng.HasFormula = False And rng.Cells.Interior.Color = RGB(255, 255, 255) Then[/COLOR]
[COLOR=#574123]cell.Font.Color = RGB(192, 0, 255)[/COLOR]
[COLOR=#574123]End If[/COLOR]
[COLOR=#574123]Next cell[/COLOR]
[COLOR=#574123]Else[/COLOR]
[COLOR=#574123]Err.Clear[/COLOR]
[COLOR=#574123]End If[/COLOR]
[COLOR=#574123]Next ws[/COLOR]
[COLOR=#574123]On Error GoTo 0[/COLOR]
[COLOR=#574123]End Sub[/COLOR]




[COLOR=#574123]Sub Hardcode()[/COLOR]
[COLOR=#574123]Dim rng As Range[/COLOR]
[COLOR=#574123]Dim cell As Range[/COLOR]
[COLOR=#574123]Dim ws As Worksheet[/COLOR]
[COLOR=#574123]On Error Resume Next[/COLOR]
[COLOR=#574123]For Each ws In ActiveWorkbook.Worksheets[/COLOR]
[COLOR=#574123]Set rng = ws.Cells.Borders(xlEdgeBottom).LineStyle <> xlNone[/COLOR]
[COLOR=#574123]If Err = 0 Then[/COLOR]
[COLOR=#574123]For Each cell In rng[/COLOR]
[COLOR=#574123]If rng.HasFormula = False Then[/COLOR]
[COLOR=#574123]If rng.Interior.Color = RGB(255, 255, 255) Then[/COLOR]
[COLOR=#574123]cell.Font.Color = RGB(192, 0, 255)[/COLOR]
[COLOR=#574123]End If[/COLOR]
[COLOR=#574123]End If[/COLOR]
[COLOR=#574123]Next cell[/COLOR]
[COLOR=#574123]Else[/COLOR]
[COLOR=#574123]Err.Clear[/COLOR]
[COLOR=#574123]End If[/COLOR]
[COLOR=#574123]Next ws[/COLOR]
[COLOR=#574123]On Error GoTo 0[/COLOR]
[COLOR=#574123]End Sub[/COLOR]

 

[Domenic]

Try...

[COLOR=darkblue]Sub[/COLOR] Hardcode()
    [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] cell [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] ActiveWorkbook.Worksheets
        [COLOR=darkblue]Set[/COLOR] rng = ws.Cells.SpecialCells(xlCellTypeConstants)
        [COLOR=darkblue]If[/COLOR] Err = 0 [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cell [COLOR=darkblue]In[/COLOR] rng
                [COLOR=darkblue]If[/COLOR] cell.Borders(xlEdgeBottom).LineStyle <> xlNone [COLOR=darkblue]Then[/COLOR]
                    [COLOR=darkblue]If[/COLOR] cell.Interior.Color = RGB(255, 255, 255) [COLOR=darkblue]Then[/COLOR]
                        cell.Font.Color = RGB(192, 0, 255)
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]Next[/COLOR] cell
        [COLOR=darkblue]Else[/COLOR]
            Err.Clear
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] ws
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[bsquad]

dammit this is awesome, thanks so much again!!