Change from ActiveSheet to ActiveWorkbook

bsquad

Board Regular
Joined
Mar 15, 2016
Messages
194
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Try...

Code:
[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

Board Regular
Joined
Mar 15, 2016
Messages
194
Thanks so much!!

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

bsquad

Board Regular
Joined
Mar 15, 2016
Messages
194

ADVERTISEMENT

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

Board Regular
Joined
Mar 15, 2016
Messages
194
sorry, with tags-
[/CODE]
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]
 
Last edited:

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Try...

Code:
[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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,128
Messages
5,599,888
Members
414,343
Latest member
JennyGarcia

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