Help to Search...

[didig@inbox.com]

Hello,

I need your help to solve the following: i would like to write something to check if in a woorkbook (so searching all the sheets) there is any value above 100% and highlitght/point to it. (Ideally I would like to rebase the sum so that it adds up to 100% - but this would be a dream. Knowing if there is any value in the woorkbook > 100% would be already great).

Thanks in advance - appreciated

[Chinchin]

Sub IsThereOver100()
Dim ws As Worksheet, cell As Range, ThereIsIndeed As Boolean

For Each ws In ActiveWorkbook.Worksheets
    If Application.WorksheetFunction.Max(ws.UsedRange) > 0.01 Then
        ThereIsIndeed = True
        For Each cell In ws.UsedRange
            If cell.Value > 0.01 Then cell.Interior.ColorIndex = 8
        Next cell
    End If
Next ws

If ThereIsIndeed Then
    MsgBox "There are cell(s) with over 100%"
Else
    MsgBox "There are no cells with over 100%"
End If
End Sub

[didig@inbox.com]

Hello Chinchin, Thanks a lot for your help . Is there a way to look for values between 100% and 102%? or alternatively to look only in cells formatted as %. The problem I have is that in these workbook there values like $100,000 that are ok but then mistakes like 100.5% that need to be corrected.

Thanks again

[didig@inbox.com]

hello, I am trying to run a small vba to check for values above 100% but it does not "see" the values abv 100%. Not sure what is wrong. I'd appreciate if someone could fix it.

Blw the code kindly provided by Chinchin. and attached the wrkbook where i tried but it does not see the 100.73 nor the 101.

Sub IsThereOver100()

Dim ws As Worksheet, cell As Range, ThereIsIndeed As Boolean

For Each ws In ActiveWorkbook.Worksheets

If (Application.WorksheetFunction.Max(ws.UsedRange) > 100) And (Application.WorksheetFunction.Max(ws.UsedRange) < 102) Then

ThereIsIndeed = True

For Each cell In ws.UsedRange

If (cell.Value > 100) And (cell.Value < 102) Then cell.Interior.ColorIndex = 6

Next cell

End If

Next ws


If ThereIsIndeed Then

MsgBox "There are cell(s) with over 100%"

Else

MsgBox "OK"

End If

End Sub

[StevenM]

Perhaps something like:

Sub ColorCellsOver100Percent()
    Dim ws As Worksheet, rgCell As Range
    
    For Each ws In ActiveWorkbook.Worksheets
        For Each rgCell In ws.UsedRange
            If rgCell.NumberFormat = "0.00%" _
            Or rgCell.NumberFormat = "0.0%" _
            Or rgCell.NumberFormat = "0%" Then
                If rgCell.Value > 1 Then rgCell.Interior.ColorIndex = 3 'red
            End If
        Next rgCell
    Next ws
End Sub

[didig@inbox.com]

Thanks a lot Steve. it works on itsefl but when I tried it in my workbook, unfortunately it did not see the 100.73... The problem i have is that I copy values from a another file and this is what i have (you can also open the file abv. for more details)

Inv % 100.73

This is the line that should do the work. But for some reasons it does not see the 100.73 in the workbook....

If (Application.WorksheetFunction.Max(ws.UsedRange) > 100) And (Application.WorksheetFunction.Max(ws.UsedRange) < 102) Then
ThereIsIndeed = True

it seems that somehow it does not see the 100.73 above as a nr. I selected the entrie sheet and formated all to nr. but still nothing. Not sure if anyone has an idea... format everyhting as nr. directly in the vba, or divide each cell by 100 and then run a % check...

Tnx a lot to everyone :-)

[StevenM]

Are you saying that you have a cell with "Inv % 100.73"?

[didig@inbox.com]

1 cell has "inv %" and the next "100.73" . It should see 100.73 but it does not.... and I do not understand why... (attaching the file here)

[StevenM]

Part of the problem is the cell where 100.73 is located is not formatted as a percent. It is formatted as a General number.
The code I gave you looked for cells which were formatted as percent.

If rgCell.NumberFormat = "0.00%" _
Or rgCell.NumberFormat = "0.0%" _
Or rgCell.NumberFormat = "0%" Then

Then it looked for values over 1 (which is 100%).

The following code uses a different method.
It searches the active sheet for "Inv %".
Then it looks at the cell one to the right.
If the value is > 100 it colors the cell red.

Sub ColorInvPercentOver100()
    Dim rgCell As Range, sAddress As String
    
    With ActiveSheet.UsedRange
        Set rgCell = .Find(What:="Inv %", LookIn:=xlValues)
        If Not rgCell Is Nothing Then
            sAddress = rgCell.Address
            Do  'Check next cell over
                If rgCell.Cells(1, 2) > 100 Then
                    rgCell.Cells(1, 2).Interior.ColorIndex = 3 'red
                End If
                Set rgCell = .FindNext(rgCell)
            Loop While Not rgCell Is Nothing And rgCell.Address <> sAddress
        End If
    End With
End Sub

[didig@inbox.com]

Thanks a lot Steve. Would it be possible to look for values between 100 and 102 in the entire workbook, color the cell and maybe have a pop up saying there are X values to check?

Thanks a lot your help is much appreciated