Find cells with value + conditional format...

[spacely]

Hello,

How do i find cells in any sheet in the whole book with value 0.09 and a conditional format that made the font bold? It's not normal bold apparently, because the built-in Find GUI doesn't find conditional bold....

Thanks!

 

[spacely]

I really need help here...

 

[Fluff]

Is the 0.09 value a hard value, or the result of a formula?

 

[spacely]

That is a fixed value...

 

[Fluff]

How about

Sub spacely()
   Dim Ws As Worksheet
   Dim Cl As Range, Rng As Range
   Dim Msg As String
   
   For Each Ws In Worksheets
      On Error Resume Next
      Set Rng = Ws.UsedRange.SpecialCells(xlConstants, xlNumbers)
      On Error GoTo 0
      If Not Rng Is Nothing Then
         For Each Cl In Rng
            If Cl.Value = 0.09 And Cl.DisplayFormat.Font.Bold Then
               Msg = Msg & vbLf & Ws.Name & "!" & Cl.Address(0, 0)
            End If
         Next Cl
      End If
   Next Ws
   MsgBox Msg
End Sub

One word of waning, if you have lots of sheets, or large amounts of data, this could take a while

 

[spacely]

Will I be able to pause it at each find, and clear out that cell? I don't need a message so much as to just find and take action at that moment....the action depends what I see in the surrounding cells

 

[Fluff]

You can't pause the macro, but you can exit it like

Sub spacely()
   Dim Ws As Worksheet
   Dim Cl As Range, Rng As Range
   
   For Each Ws In Worksheets
      On Error Resume Next
      Set Rng = Ws.UsedRange.SpecialCells(xlConstants, xlNumbers)
      On Error GoTo 0
      If Not Rng Is Nothing Then
         For Each Cl In Rng
            If Cl.Value = 0.09 And Cl.DisplayFormat.Font.Bold Then
               Ws.Select
               Cl.Select
               Exit Sub
            End If
         Next Cl
      End If
   Next Ws
End Sub

 

[spacely]

The 1st one collects a list of addresses, but the 2nd one stops at the 1st occurrence and doesn't continue. Is there a way to save up the list from 1st method, and then loop through each occurrence pausing for a user action? Or in the 2nd one, just start from the active cell and maybe it will continue to the next occurrence?

 

[spacely]

An idea: store the list of addresses in a sheet, and make a GoTo function that goes to the 1st item in the list, and then also deletes that first item. Then, when that GoTo is executed again, it's doing the 2nd entry, and so forth to the end. Maybe using a named range that gets resized each time.

Kinda messy

 

[Fluff]

This will put a list of sheet names & addresses on a sheet called "List", which you can then use to find & deal with the cells as appropriate

Sub spacely()
   Dim Ws As Worksheet
   Dim Cl As Range, Rng As Range
   
   For Each Ws In Worksheets
      On Error Resume Next
      Set Rng = Ws.UsedRange.SpecialCells(xlConstants, xlNumbers)
      On Error GoTo 0
      If Not Rng Is Nothing Then
         For Each Cl In Rng
            If Cl.Value = 0.09 And Cl.DisplayFormat.Font.Bold Then
               With Sheets("List").Range("A" & Rows.Count).End(xlUp)
                  .Offset(1).Value = Ws.Name
                  .Offset(1, 1).Value = Cl.Address(0, 0)
               End With
            End If
         Next Cl
      End If
      Set Rng = Nothing
   Next Ws
End Sub