excel:formatting using a macro

alancool

Hi Folks

Im having a small formatting issue in excel that I would like to iron out:rolleyes:
I loop through column A to find a string when its found I want to:
make the the string bold
change the fill in colour to green
and increment a counter

at the moment it
makes the string bold
changes the font colour to green
and increments the counter

Can anyone help??




Sub macro1
Dim Counter80o As Integer

Counter80o = 0

Range("A1:A10000").Select
For rwIndex = 1 To 10000
For colIndex = 1 To 1
With Worksheets("Sheet1").Cells(rwIndex, colIndex)
If .Value = "80/tcp open http" Then .Font.Color = vbGreen
If .Value = "80/tcp open http" Then Counter80o = Counter80o + 1
If .Value = "80/tcp open http" Then .Font.Bold = True
End With
Next colIndex
Next rwIndex
End Sub

Peace

alancool wrote:

Sub macro1
Dim Counter80o As Integer

Counter80o = 0

Range("A1:A10000").Select
For rwIndex = 1 To 10000
For colIndex = 1 To 1
With Worksheets("Sheet1").Cells(rwIndex, colIndex)
If .Value = "80/tcp open http" Then .Font.Color = vbGreen
If .Value = "80/tcp open http" Then Counter80o = Counter80o + 1
If .Value = "80/tcp open http" Then .Font.Bold = True
End With
Next colIndex
Next rwIndex
End Sub

You shouldn't be changing the font to be green, you should be setting the fillcolor or the backcolor.

Peace

I was playing with the vba that comes with excel and this worked for me

ActiveCell.Interior.Color = vbRed

Edser

Try .

If .Value = "80/tcp open http" Then .Interior.ColorIndex = 4

Ed

Phileas Fogg

You could do the following instead, I prefer to use activecell.offset for looping through cells or ranges, it removes the need for all those nested if statements, I'd also remove the With/End With but that's a personal preference. This works in Excel 2000 (for the first 11 rows ):

Sub Macro2()

    Dim counter80o As Integer
    counter80o = 0
    
    Range("A1:A11").Select
    
    While counter80o < 10000 
        ActiveCell.Offset(1, 0).Activate
        With ActiveCell
            If .Text = "80/tcp open http" Then
                .Interior.Color = vbGreen
                .Font.Bold = True
            End If
        End With
        counter80o = counter80o + 1
    Wend
    
End Sub

<edit> Oops, forgot you wanted to fill in.

alancool

Folks tanx for the help it was the Interior.Color attribute that I simply didn't know about your help is appreciated.

Phileas Fogg: Thanks I didn't like the nested ifs either.