Excel Replace Function with Format

[krammer]

Does anyone know how to use the excel replace function with font formatting? I have tried several different combinations with no success...here's one for example:

objExcel.Range("C:C").Replace "warning", "Warning" AND objExcel.Range("C:C").ReplaceFormat.Font.ColorIndex = 3

Here is also what it would look like with an excel macro...but would have to be transformed into vbscript:

Columns("C:C").Select
    Range("C3").Activate
    With Application.ReplaceFormat.Font
        .Subscript = False
        .ColorIndex = 3
    End With
    Selection.Replace What:="warning", Replacement:="Warning", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=True

 

[tsuji]

The literal transposition is this.
[tt]
With objExcel
.Columns("C:C").Select
.Range("C3").Activate
With .ReplaceFormat.Font
.Subscript = False
.ColorIndex = 3
End With

'define a couple of variables corresponding to named parameter settings (just for easy reading for vba in mind)
xlpart=2
xlbyrows=1
matchcase=false
replaceformat=true

.selection.replace "warning","Warning",xlpart,xlbyrows,matchcase,,,replaceformat
End With
[/tt]

 

[krammer]

Yeah wasn't exactly working...I ended up just doing this for the string:

	If strType = "warning" Then 
	  objExcel.Cells(x,y1).Interior.ColorIndex = 6
	  objExcel.Cells(x,y1).Font.Bold = True
	End If

	If strType = "error" Then
	  objExcel.Cells(x,y1).Font.ColorIndex = 2
	  objExcel.Cells(x,y1).Interior.ColorIndex = 3
	  objExcel.Cells(x,y1).Font.Bold = True
	End If

And then adding this after looping through the strings:

objExcel.Range("C:C").Replace "warning", "Warning"
objExcel.Range("C:C").Replace "error", "Error"

Probably not the best way, but its working at the moment...

 

[tsuji]

>Yeah wasn't exactly working...
Working? why should it work to produce your second list... with "error" etc etc? You didn't know what specific vbs need to be scripted for the job, I showed it to you. You miss the whole point of the exercise.

 

[krammer]

Ummm, the second list doesn't matter, if the first one doesn't work then why would the second?

 

[tsuji]

List or non-list, you keep them in your drawer. Nobody knows what you're talking about. You miss the point of what scripting is about.

 

[krammer]

All I'm doing is trying to automate the process of coloring and renaming the cells in column C that have "warning" and "error" in them...does that help?

 

[tsuji]

>...does that help?
If you need help, you should ask and give sufficient info, the forum is not asking.

 

[krammer]

Ok...I thought what I gave was more then enough to figure out what I was asking.

Don't worry about it then.