Conditionally apply a cell fill

Dear Experts:
   below macro code ...
... searches for cells with text entries in column B.
... if found these cells are filled with a cell fill of RGB (222, 222, 222).

This code was furnished courtesy of 'nutsch'. It works very well!

In the meantime new requirements surfaced.

I would like to get this macro tweaked / re-written so that the conditions for the cell fill are as follows:

1) the cell has to have a text entry (ie. not numeric) and
2) one character in these cells has to be a ? (Question mark) and
3) the string must start with a uppercase letter.

I have attached a sample file for your convenience.

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

Regards, Andreas

 Conditional-Cell-Fill.xlsx
Sub FillCellsOnCondition()

Dim lLastRow As Long, lLoop As Long

lLastRow = Cells(Rows.Count, "B").End(xlUp).Row


For lLoop = 1 To lLastRow
   If Not (IsNumeric(Cells(lLoop, 2))) Then Cells(lLoop, 2).Interior.Color = RGB(222, 222, 222)
Next

End Sub

Open in new window

Andreas HermleTeam leaderAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
kemi67Connect With a Mentor Commented:
try this
 
Sub FillCellsOnCondition()

Dim lLastRow As Long, lLoop As Long

lLastRow = Cells(Rows.Count, "B").End(xlUp).Row


For lLoop = 1 To lLastRow

   If Not (IsNumeric(Cells(lLoop, 2))) And InStr(Cells(lLoop, 2), "?") > 0 And UCase(Left(Cells(lLoop, 2), 1)) = Left(Cells(lLoop, 2), 1) Then
       Cells(lLoop, 2).Interior.Color = RGB(222, 222, 222)
   End If
Next

End Sub

Open in new window

0
 
StephenJRConnect With a Mentor Commented:
A marginally different approach:
Sub FillCellsOnCondition()

Dim r As Range

For Each r In Columns(2).SpecialCells(xlCellTypeConstants, xlTextValues)
    If InStr(r, "?") > 0 And Left(r, 1) = UCase(Left(r, 1)) Then
        r.Interior.Color = RGB(222, 222, 222)
    End If
Next r

End Sub

Open in new window

0
 
Rob HensonFinance AnalystCommented:
If either of options 2 or 3 are True then option 1 will automatically be true so can be ignored because both 2 & 3 will be checking for text entry.

Thanks
Rob H
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Rory ArchibaldConnect With a Mentor Commented:
NFP

You could also use Like:
Sub ColourCells()
    Dim rngCell As Range
    For Each rngCell In Range("B:B").SpecialCells(xlCellTypeConstants, 2)
        If rngCell.Text Like "[A-Z]*[?]" Then
            rngCell.Interior.Color = RGB(222, 222, 222)
        End If
    Next rngCell
End Sub

Open in new window

0
 
Andreas HermleTeam leaderAuthor Commented:
Hi everyone:

thank you very much  for your overwhelming support. All of the codes are working although I had to substitute (xlCellTypeFormulas) for (xlCellTypeConstants) on rorya's and Stephen's code.
I did not mention this in my post that the values are linked to some other sheet.

I suggest splitting the points with Kemi getting awarded a little bit more. His code works without any tweaking. Anyhow I would like to split the points since all the different approaches are of great value to me.

Again, thank you very much for your great/professional support.

Regards, Andreas
0
 
Andreas HermleTeam leaderAuthor Commented:
Great support from everybody. Really superb forum!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.