Link to home
Start Free TrialLog in
Avatar of Andreas Hermle
Andreas HermleFlag for Germany

asked on

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
SOLUTION
Avatar of wchh
wchh

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Nico Bontenbal
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

ah, I forgot about the msgbox. Solution of wchh is the correct one.
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
last end if should have been end sub
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Andreas Hermle

ASKER

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