Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing font colour
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing font colour
One way:-
Sub stantial() Dim myRange As Range Set myRange = Range("H1:H200") For Each c In myRange c.Select If c.Value = "PRO" Then Selection.EntireRow.Select Selection.Font.ColorIndex = 3 End If Next End Sub Mike "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing font colour
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing font colour
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing font colour
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing font colour
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing font colour
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing font colour
in lieu of a macro, you could also use conditional formatting:
select a5 to g5 & do format conditional formatting choose "cell value is" "equal to" in the next field, write (without quotes) PRO then select i5 to z5 & do the same thing then select a5:z5 and pull down as far as you need it. hth susan On Jun 18, 8:02 am, 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- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing font colour
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing font colour
aaack! ignore that! that'll only work if those cells contain "pro".
you need "formula is" & i can't think of the formula right @ this moment............. =$H5="PRO" i think that'll work. when you drag it down the row number will change but not the column. sorry for the mix-up! susan On Jun 18, 8:18 am, Susan wrote: in lieu of a macro, you could also use conditional formatting: select a5 to g5 & do format conditional formatting choose "cell value is" "equal to" in the next field, write (without quotes) PRO then select i5 to z5 & do the same thing then select a5:z5 and pull down as far as you need it. hth susan |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing font colour
Many thanks to Mike, Tom & Susan - I went for the conditional formatting
option in the end as I would like the font to change automatically regardless of user action Jock "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing font colour to red & orange | Excel Discussion (Misc queries) | |||
Changing font colour depending on cell value | Excel Worksheet Functions | |||
How do I stop the font from changing colour automatically? | Excel Discussion (Misc queries) | |||
Changing font colour in some cells | New Users to Excel | |||
changing font colour of row when cell contains date | Excel Programming |