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

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:murbro
  • 3
  • 3
LVL 50

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

ID: 40410101
Hi. Does that format just column B
LVL 50

Expert Comment

ID: 40410165
Yes it should

line 11

Range("B:B"). etc.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.


Author Comment

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

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

ID: 40410349
great thanks. No conditional formatting

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

790 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