[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Conditionally apply a cell fill

Posted on 2011-09-04
Medium Priority
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 600 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 800 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 34

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
Independent Software Vendors: 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!

LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 600 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

872 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