Excel VBA Format all cells with same value in a column yellow fill

Posted on 2014-10-28
Last Modified: 2014-10-29

What Excel VBA code would I use to find and format all cells in column B
with the same value (e.g. xyz) with a background fill colour of yellow
Question by:Murray Brown
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 51

Expert Comment

ID: 40410060

pls try

Sub Macro()
strText = "xyz"

With Application.ReplaceFormat.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 65535
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
Range("B:B").Replace strText, strText, xlWhole, , , , , True

End Sub

Open in new window


Author Comment

by:Murray Brown
ID: 40410101
Hi. Does that format just column B
LVL 51

Expert Comment

ID: 40410165
Yes it should

line 11

Range("B:B"). etc.
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.


Author Comment

by:Murray Brown
ID: 40410266
Hi. It looks like that line just replaces values not formats in the column, or am I missing something
LVL 51

Accepted Solution

Rgonzo1971 earned 500 total points
ID: 40410343
The True at the end is for Replace format

Is there Conditional Formatting  in these cells?

Author Closing Comment

by:Murray Brown
ID: 40410349
great thanks. No conditional formatting

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

691 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