Conditionally apply a cell fill

Posted on 2011-09-04
Last Modified: 2012-05-12
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

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)

End Sub

Open in new window

Question by:AndreasHermle
LVL 24

Assisted Solution

StephenJR earned 150 total points
ID: 36482623
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


Accepted Solution

kemi67 earned 200 total points
ID: 36482629
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

End Sub

Open in new window

LVL 32

Expert Comment

by:Rob Henson
ID: 36483257
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.

Rob H
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 150 total points
ID: 36484012

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


Author Comment

ID: 36484819
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

Author Closing Comment

ID: 36484843
Great support from everybody. Really superb forum!

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question