In case you missed it Jock, you have to run the macro to get it to do
anything.

If you want it to be interactive based on the users actions, better to skip
the code and use conditional formatting.

--
Regards,
Tom Ogilvy


"Mike H" wrote:

ALT+Fll
Right click ThisWorkbook
Insert module
Double click the newly inserted module
Paste in code
Close VB editor
select desired worksheet
With the words PRO in column H (H1 - H200)
Tools|Macros
select the macro and Run

Mike
"Jock" wrote:

Still doesn't work for me. So, to avoid appearing thick, I created a new work
book, entered some data and PRO in one or two cells in "H". Copied the code
(ALT + F11) and saved. I still can't get it to work. :(


Jock


"Mike H" wrote:

Jock,

It's a standard module (ALT+F11 insert module) and will look at the active
sheet i.e the sheet you are on when the macro is called.

Mike

"Jock" wrote:

Thanks to Mike & Steve. There are blanks so I tried Mikes suggestion but I
can't get it to work. Have tried in both 'worksheet' and 'general' but
doesn't work for me.
Would autoformatting and a drop down list (in 'H') affect anything?

Jock


"steve_doc" wrote:

Hi Jock
1 aproach is

using a loop through a range, assuming that your range contains no
blanks(empty)
Sub TEST_Colour()
Dim wb As Workbook
Dim ws As Worksheet
Dim rg As Range

Dim stCrit As String

Set wb = ThisWorkbook
Set ws = wb.Worksheets(1)
Set rg = ws.Range("A2")
stCrit = "PRO"

Application.ScreenUpdating = False ' increases speed on long loops

Do Until IsEmpty(rg) 'perform loop till rg is empty
If rg.Offset(0, 7) = stCrit Then 'logic comparison
rg.EntireRow.Font.Color = vbRed 'if logic is true text colour = red
Set rg = rg.Offset(1, 0) 'increase range by 1 row
Else
Set rg = rg.Offset(1, 0)
End If
Loop

Application.ScreenUpdating = True
End Sub

HTH
"Jock" wrote:

Using vba, how can I change font colour to red for the entire row (A5 - Z5)
if cell H5 contains "PRO"? And how do I then copy this code so it affects all
rows (up to 200) in the worksheet?
--
tia

Jock