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
Solved

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

Posted on 2014-10-28
6
182 Views
Last Modified: 2014-10-29
Hi

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
0
Comment
Question by:murbro
  • 3
  • 3
6 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40410060
Hi,

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

Regards
0
 

Author Comment

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

Expert Comment

by:Rgonzo1971
ID: 40410165
Yes it should

line 11

Range("B:B"). etc.
0
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

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

Accepted Solution

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

Is there Conditional Formatting  in these cells?
0
 

Author Closing Comment

by:murbro
ID: 40410349
great thanks. No conditional formatting
0

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