Macro Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I run the following macro commands and they work fine. It simply looks at the
cell and if it matches the text in this case B.S. it replaces it with B.S.
with the cell color coded in this case color 3. My question is that this
macro requires that the cell entry be B.S., how could I look at the cells
value as opposed to what's entered. I would prefer that the cell receive the
B.S. value by using a formula that picks up the B.S value. But the macro
below at this time simply sees the formula and not the value.

Any help would be greatly appreciated!

Range("M7:CK100").Select
Application.ReplaceFormat.Interior.ColorIndex = 3
Selection.Replace What:="B.S.", Replacement:="B.S.", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=True
 
Sub McCloud()
Set r = Range("M7:CK100")
For Each rr In r
With rr
If .Value = "B.S." Then
.Interior.ColorIndex = 3
End If
End With
Next
End Sub
 
If you're not using Format|Conditional formatting, you may want to use that
instead of a macro:

If you select the range first (M7:ck100)
then with M7 the activecell
format|Conditional formatting
formula is:
=countif(m7,"*b.s.*")>0

And give it a pretty format.
 
If I had seen that requirement in your original post, I wouldn't have suggested
conditional formatting in xl2003 and below.

BTW, xl2007 allows lots more rules.
 
Could you help me with Gary's student reply. It looks like it should work but
I keep getting errors on it.

Thanks for all your time
 
If you have 12 different rules, I'm not sure I'd want to loop through all the
cells in that range 12 different times.

Maybe you could use something like:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myCell As Range
Dim myWords As Variant
Dim myColors As Variant
Dim iCtr As Long
Dim FirstAddress As String
Dim FoundCell As Range

Set myRng = Worksheets("sheet1").Range("M7:CK100")
'remove any existing colors?????
myRng.Interior.ColorIndex = xlNone

myWords = Array("b.s.", "hi", "another")
myColors = Array(3, 5, 9)

If UBound(myWords) <> UBound(myColors) Then
MsgBox "error--number of words and colors don't match!"
Exit Sub
End If

For iCtr = LBound(myWords) To UBound(myWords)
FirstAddress = ""
With myRng
Set FoundCell = .Cells.Find(what:=myWords(iCtr), _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'that word wasn't found. Do nothing
Else
FirstAddress = FoundCell.Address
Do
FoundCell.Interior.ColorIndex = myColors(iCtr)
Set FoundCell = .FindNext(after:=FoundCell)
If FoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
End If
End With
Next iCtr

End Sub

You'll update the myWords array with your list of 12 words. And update the
myColors with the corresponding numbers.


Could you help me with Gary's student reply. It looks like it should work but
I keep getting errors on it.

Thanks for all your time
Click to expand...
 
Back
Top