Hi, my beautiful saviours

[buntykins]

How's it going? I'm hoping one of you can save me again. I need some VB code that will look down column A, and if a cell contains the word "Completed", then I need it to shade that cell green, but I need it to miss out the other cells.

Does anyone know how to do this?

Janie
xx

[sann0638]

Will a conditional format not achieve this?

[buntykins]

Hiya, yes conditional formatting would do it, but I need it in VBA as it will be part of an ontime function. IE: At 10:15am it checks the row and shades accordingly, then at 11am it checks again and shades a different colour. I have everything else worked out apart from the shading part

[Ivan F Moala]

On 2002-05-21 07:07, buntykins wrote:

How's it going? I'm hoping one of you can save me again. I need some VB code that will look down column A, and if a cell contains the word "Completed", then I need it to shade that cell green, but I need it to miss out the other cells.

Does anyone know how to do this?

Janie
xx

Try
Note: Assumes the Completed is a constant
and NOT a result of a formula.

Sub TESTER()

Dim rMyRg As Range

Dim rCell As Range



Set rMyRg = Columns("A:A").SpecialCells(2, 23)



For Each rCell In rMyRg

    If UCase(rCell.Text) = "COMPLETED" Then

        rCell.Interior.ColorIndex = 35

    End If

Next

End Sub

[buntykins]

Thanks Ivan that's superb! Could you answer me one more thing though? This might be a bit tricky so I apologise.

Can you have it so that if the cell's shading is red and it has "Completed" written in it, then it format's the cell to turn amber?

Sorry to be a pest

Janie

xx

[kkknie]

What's the 23 for in the specialcells method?

K

[Ivan F Moala]

On 2002-05-21 07:24, buntykins wrote:

Thanks Ivan that's superb! Could you answer me one more thing though? This might be a bit tricky so I apologise.

Can you have it so that if the cell's shading is red and it has "Completed" written in it, then it format's the cell to turn amber?

Sorry to be a pest

Janie

xx

Hi Janie
How is this Turning RED ??
As running this macro turns them ALL to green
so there will be no RED. The reason I ask is
That if you have another routine running in the Background eg Application events the it
may be advisable place code that does everything...if not then disregard and use this.

Sub TESTER()

Dim rMyRg As Range

Dim rCell As Range



Set rMyRg = Columns("A:A").SpecialCells(2, 23)



For Each rCell In rMyRg

    If UCase(rCell.Text) = "COMPLETED" Then

        If rCell.Interior.ColorIndex = 3 Then

            '// change color codes you want

            rCell.Interior.ColorIndex = 40

        Else

            rCell.Interior.ColorIndex = 35

        End If

    End If

Next

End Sub

[buntykins]

Thanks Ivan, that's brilliant. It has got quite a few conditions, but I think Ive got it sorted now

Thanks again

xxxxx

[Ivan F Moala]

On 2002-05-21 07:27, kkknie wrote:
What's the 23 for in the specialcells method?

K

K
The 23 is the xlCellTypeConstants
ie. Numbers,Text,Logicals,Errors