Link to home
Start Free TrialLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

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
Avatar of Rgonzo1971
Rgonzo1971
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
Avatar of Murray Brown
Murray Brown
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Hi. Does that format just column B
Avatar of Rgonzo1971
Rgonzo1971
Yes it should

line 11

Range("B:B"). etc.
Avatar of Murray Brown
Murray Brown
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Hi. It looks like that line just replaces values not formats in the column, or am I missing something
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Murray Brown
Murray Brown
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

great thanks. No conditional formatting