Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

Search for Cells with a question mark and shade them

Dear Experts:

I would like to do achieve the following with the aid of a VBA macro:

Search for cells on the active worksheet that contain a QUESTION MARK and fill those cells with a RGB fill (RGB 191, 191, 191) .
The number of cells found and shaded should be displayed in a MsgBox.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
AndreasHermle
Asked:
AndreasHermle
3 Solutions
 
wchhCommented:
try macro below
Sub Question_mark()
    Dim cnt As Long
    For Each cel In ActiveSheet.UsedRange
        If Not IsError(cel.Value) Then
        If InStr(cel.Value, "?") Then
            cel.Interior.Color = 12566463
            cnt = cnt + 1
        End If
        End If
    Next cel
    MsgBox (cnt)
End Sub

Open in new window

0
 
Nico BontenbalCommented:
Sub ShadeQuestionMark()
    Dim c As Range
    For Each c In ActiveSheet.UsedRange.Cells
        If c.Value Like "*[?]*" Then
            c.Interior.Color = RGB(191, 191, 191)
        End If
    Next
End Sub

Open in new window

0
 
Nico BontenbalCommented:
ah, I forgot about the msgbox. Solution of wchh is the correct one.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Eric ZwiekhorstTSE service engineerCommented:
Sub ShdCntLst()
dim R as range
dim lst as string
dim i as integer
For Each RIn ActiveSheet.UsedRange.Cells
        If R.Value Like "*[?]*" Then
            R.Interior.Color = RGB(191, 191, 191)
            lst = lst & " : " & R.adress
            i=i+1
        End If
    Next
MSGBOX ("found " & i & "Question markes in the following cells : " & lst)
end if
0
 
Eric ZwiekhorstTSE service engineerCommented:
last end if should have been end sub
0
 
Eric ZwiekhorstTSE service engineerCommented:
Look at file..
ShdCntLST.xls
0
 
SiddharthRoutCommented:
Here is a fastest way. This is fastest as it doesn't loop through each cell in the worksheet.

Sub Sample()
    Dim oRange As Range, aCell As Range, bCell As Range
    Dim ws As Worksheet
    Dim ExitLoop As Boolean
    Dim SearchString As String, FoundAt As String
    
    On Error GoTo Err
    
    Set ws = Worksheets("Sheet1") '<~~ Replace this with your sheet name
    Set oRange = ws.Cells

    SearchString = "?"
    
    Set aCell = oRange.Find(What:=SearchString, LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        Set bCell = aCell
        If InStr(1, aCell.Value, "?") Then
            FoundAt = aCell.Address
            aCell.Interior.Color = RGB(191, 191, 191)
        End If
        Do While ExitLoop = False
            Set aCell = oRange.FindNext(After:=aCell)

            If Not aCell Is Nothing Then
                If aCell.Address = bCell.Address Then Exit Do
                If InStr(1, aCell.Value, "?") Then
                    aCell.Interior.Color = RGB(191, 191, 191)
                    FoundAt = FoundAt & ", " & aCell.Address
                End If
            Else
                ExitLoop = True
            End If
        Loop
        MsgBox "The Search String has been found these locations: " & FoundAt
    Else
        MsgBox SearchString & " not Found"
    End If
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

Open in new window


Sid
0
 
AndreasHermleAuthor Commented:
Dear all,

great job from all of you. It is always hard to distribute the points equitably. I really appreciate your professionalism. This forum deserves its name.

Thank you very much . Regards, Andreas
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now