I use excel 2003 but this method should also work for Excel 2000 as I've tested
it on both versions. I had to make a similar macro that would search a column
of data for a specific value and then change the old value to a new value as
well as apply conditional formatting such as font style, font color color, style
and/or cell background color, font name, font size and search criteria (to find
partial or exact cell values). Here's how I did it:
Set up a worksheet where you can enter this conditional formatting
criteria...lets call it "ColG" (assuming that the conditional formatting is set
up to search only in column G.
The data it would search for and apply the conditional formatting would be kept
in a separate worksheet...let's call that worksheet "Data".
Worksheet "ColG" will be used to set up your edit-replace (Conditional
Formatting) criteria...for example, sheet "ColG" apply conditional formatting to
column G of your "Data" worksheet...
Basically the column headings for worksheet "ColG" would be set up as follows:
Cell A1 = Replace Old text ...Cells A2 and down this column would specify what
the macro criterion should search for...e.g. "ABC"
Cell B1 = With New Text ...Cells B2 and down this columnwould specify what the
criterion would be replaced as...e.g. "CDE"...so ABC would be replaced with
CDE...or you could keep it the same if you are just wanting to change the cell
format.
Cell C1 = select Font Style ...Cells C2 and down this column would be set up
with In List Data Validation allowing the user to select Regular, Bold, Italic
or Bold Italic...)
Cell D1 = Specify Color Code ...Cells D2 and down this column would be set up
with In List Data Validation allowing the user to selct a number between 1 - 10.
These number represent the color code:
1 = Black
2 = White
3 = Red
4 = Green
5 = Blue
6 = Yellow
7 = Magenta
8 = Light Blue
Cell E1 = Specify Cell Color Code ...Cells E2 and down this column would be set
up
with In List Data Validation allowing the user to selct a number
between 1 - 10. These number represent the color code:
1 = Black
2 = White
3 = Red
4 = Green
5 = Blue
6 = Yellow
7 = Magenta
8 = Light Blue
Cell F1 = Select Font Name ...Cells F2 and down this column would be set up with
In List Data Validation allowing the user to select Arial, Times New Roman,
Comic Sans MS or Century Gothic
Cell G1 = Specify Font Size...Cells G2 and down this column would specify the
font size (e.g. 8, 10, 11, 12, etc.)
Cell H1 = Select Partial or Exact Match ...Cells H2 and down this column would
be set up with In List Data Validation allowing the user to select "Partial" or
"Exact" (e.g. if "Partial" is chosen, then a cell specifying "ABCDEF" would be
changed because it contains "ABC"...or if "Exact" is chose, then cells that
equal only "ABC" would be changed)
Then in a separate worksheet, you would put your actual data that you want this
conditional formatting to search and replace...(e.g. Worksheet "Data")
I assigned variables in the code to represent the values within worksheet "ColG"
(old text, new text, font style, font
color color, style and/or cell background color, font name, font size
and search criteria). I then programmed a loop to run through the the variably
assigned cell values
and replace the cells in a specified column (e.g. column G of worksheet "Data" )
until the next cell downward in worksheet "ColG"
would be
null...meaning that it's reached the end of the conditional formatting criteria)
The code would look like this:
Sub Col_G_Rep()
'
' Col_G_Replace Macro
' Macro searches in Worksheet "Data" for partial or exact cell contents and
replaces the old text with the new text and conditional cell formatting.
'DO NOT change to order of the variables below!
Dim origT As String 'old text
Dim newT As String 'new text
Dim sFont As String 'font style
Dim sIndex As Integer 'font color
Dim iIndex As Integer 'cell color
Dim iFont As String 'font name
Dim iSize As Integer 'font size
Dim iCont As String 'specifies partial or exact cell contents
Sheets("ColG").Select 'Selects the worksheet that contains the conditional
formatting criteria
Range("A2").Select 'A1 is your column Heading so you do not what to select that.
While ActiveCell.Value <> "" 'DO NOT change to order of the ActiveCell VBA code
below!
'old text
origT = ActiveCell.Value
ActiveCell.Offset(0, 1).Select 'activates 1 cell to the right
'new text
newT = ActiveCell.Value
ActiveCell.Offset(0, 1).Select 'activates 1 cell to the right
'font style
sFont = ActiveCell.Value
ActiveCell.Offset(0, 1).Select 'activates 1 cell to the right
'font color
sIndex = ActiveCell.Value
ActiveCell.Offset(0, 1).Select 'activates 1 cell to the right
'cell background color
iIndex = ActiveCell.Value
ActiveCell.Offset(0, 1).Select 'activates 1 cell to the right
'font name
iFont = ActiveCell.Value
ActiveCell.Offset(0, 1).Select 'activates 1 cell to the right
'font size
iSize = ActiveCell.Value
ActiveCell.Offset(0, 1).Select 'activates 1 cell to the right
'match exact or partial cell contents
iCont = ActiveCell.Value
ActiveCell.Offset(0, 1).Select 'activates 1 cell to the right
Sheets("Data").Select ' the worksheet where the data is stored and where
conditional formatting will be applied.
Range("G2").Select ' G1 contains your column heading so you don't what it to
select that.
Range(Selection, Selection.End(xlDown)).Select 'selects cells in Column G
from Range G2 and downward...note that cells cannot be null until the end of
your column data.
Application.ReplaceFormat.Clear
Application.ReplaceFormat.Interior.ColorIndex = iIndex
With Application.ReplaceFormat.Font
.Name = iFont
.FontStyle = sFont
.Size = iSize
.ColorIndex = sIndex
End With
If iCont = ("Partial") Then
Selection.Replace What:=origT, Replacement:= newT, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True
ElseIf iCont = ("Exact") Then
Selection.Replace What:=origT, Replacement:= newT, LookAt:= _
xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True
End If
Sheets("ColG").Select
ActiveCell.Offset(1, -8).Select 'this moves to the next row and activates
the cell in column A of worksheet"ColG". I will reassign the values of this next
row to the variables
Wend
Sheets("Paste Data Here").Select
Range("A2").Select
End Sub