Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to update row format
I have some VBA code to change the font color of a row; however,
nothing changes... any suggestions? It starts with a "change event" that triggers the function: Private Sub Worksheet_Change(ByVal target As Range) Application.Run UpdateRowColor(target) then the function runs like this: Function UpdateRowColor(target) 'This section effects changes to the "Project Phase" column If Not Intersect(target, Range("A:A")) Is Nothing Then If target.Count 1 Then Exit Function Else Select Case target.Value Case Is = "CA" Range(target.Row & ":" & target.Row).Font.ColorIndex = 45 Case Is = "CD" ... (one (but only 1) of the cells in any given row has a "conditoinal formatting") thanks in advance, mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to update row format
On Jun 2, 8:53*am, Mark Kubicki
wrote: I have some VBA code to change the font color of a row; however, nothing *changes... * any suggestions? It starts with a "change event" that triggers the function: * * * * * * Private Sub Worksheet_Change(ByVal target As Range) * * * * * * * * Application.Run UpdateRowColor(target) then the function runs like this: * * * * * * Function UpdateRowColor(target) * * * * * * 'This section effects changes to the "Project Phase" column * * * * * * If Not Intersect(target, Range("A:A")) Is Nothing Then * * * * * * * * If target.Count 1 Then * * * * * * * * * * Exit Function * * * * * * * * Else * * * * * * * * * * Select Case target.Value * * * * * * * * * * Case Is = "CA" * * * * * * * * * * * * Range(target.Row & ":" & target.Row).Font.ColorIndex = 45 * * * * * * * * * * Case Is = "CD" * * * * * * * * * * * * *... (one (but only 1) of the cells in any given row has a "conditoinal formatting") *thanks in advance, *mark This works fine for me. In the sheet that you want this to take place in: Private Sub Worksheet_Change(ByVal target As Range) Application.Run UpdateRowColor(target) End Sub In a standard module: Function UpdateRowColor(target As Range) 'This section effects changes to the "Project Phase" column If Not Intersect(target, Range("A:A")) Is Nothing Then If target.Count 1 Then Exit Function Else With target Select Case .Value Case Is = "CA" Rows(.Row).Font.ColorIndex = 45 Case Is = "CD" Rows(.Row).Font.ColorIndex = 5 End Select End With End If End If End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to update row format
Hi Mark,
Completing your code segments, the code works for me. Have you tried setting break points and stepping through the code? A written the code responds to single cell entries in column A and it is case sensitive. --- Regards. Norman "Mark Kubicki" wrote in message ... I have some VBA code to change the font color of a row; however, nothing changes... any suggestions? It starts with a "change event" that triggers the function: Private Sub Worksheet_Change(ByVal target As Range) Application.Run UpdateRowColor(target) then the function runs like this: Function UpdateRowColor(target) 'This section effects changes to the "Project Phase" column If Not Intersect(target, Range("A:A")) Is Nothing Then If target.Count 1 Then Exit Function Else Select Case target.Value Case Is = "CA" Range(target.Row & ":" & target.Row).Font.ColorIndex = 45 Case Is = "CD" ... (one (but only 1) of the cells in any given row has a "conditoinal formatting") thanks in advance, mark |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to update row format
HMPFT!?... makes sense, but still nothing
"JW" wrote: On Jun 2, 8:53 am, Mark Kubicki wrote: I have some VBA code to change the font color of a row; however, nothing changes... any suggestions? It starts with a "change event" that triggers the function: Private Sub Worksheet_Change(ByVal target As Range) Application.Run UpdateRowColor(target) then the function runs like this: Function UpdateRowColor(target) 'This section effects changes to the "Project Phase" column If Not Intersect(target, Range("A:A")) Is Nothing Then If target.Count 1 Then Exit Function Else Select Case target.Value Case Is = "CA" Range(target.Row & ":" & target.Row).Font.ColorIndex = 45 Case Is = "CD" ... (one (but only 1) of the cells in any given row has a "conditoinal formatting") thanks in advance, mark This works fine for me. In the sheet that you want this to take place in: Private Sub Worksheet_Change(ByVal target As Range) Application.Run UpdateRowColor(target) End Sub In a standard module: Function UpdateRowColor(target As Range) 'This section effects changes to the "Project Phase" column If Not Intersect(target, Range("A:A")) Is Nothing Then If target.Count 1 Then Exit Function Else With target Select Case .Value Case Is = "CA" Rows(.Row).Font.ColorIndex = 45 Case Is = "CD" Rows(.Row).Font.ColorIndex = 5 End Select End With End If End If End Function |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to update row format
tried that... still nothing
also tried changing the code to: Case Is = "CA" Rows(target.Row & ":" & target.Row).Select Selection.Font.ColorIndex = 45 still nothing... is there something outside this code that could be inhibiting the action? "Norman Jones" wrote: Hi Mark, Completing your code segments, the code works for me. Have you tried setting break points and stepping through the code? A written the code responds to single cell entries in column A and it is case sensitive. --- Regards. Norman "Mark Kubicki" wrote in message ... I have some VBA code to change the font color of a row; however, nothing changes... any suggestions? It starts with a "change event" that triggers the function: Private Sub Worksheet_Change(ByVal target As Range) Application.Run UpdateRowColor(target) then the function runs like this: Function UpdateRowColor(target) 'This section effects changes to the "Project Phase" column If Not Intersect(target, Range("A:A")) Is Nothing Then If target.Count 1 Then Exit Function Else Select Case target.Value Case Is = "CA" Range(target.Row & ":" & target.Row).Font.ColorIndex = 45 Case Is = "CD" ... (one (but only 1) of the cells in any given row has a "conditoinal formatting") thanks in advance, mark |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to update row format
Hi Mark,
As indicated the code works for me. Your response: tried that... still nothing adds little that might be used futher to assist you, especially as you provide no indication of your experience when you implemented my suggestion: Have you tried setting break points and stepping through the code? --- Regards. Norman |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to update row format
sorry about that (by now I know I should be more explicit
I did set break points, and I don't see anything unusual. It steps thru each line without issue... (it could be there is "something" going on, but, being self-tasught, I'm not certain what else to look for. This is virtually the only code in the workbook. In it's reduced version, this is the entire function: Function UpdateRowColor(target As Range) 'This section effects changes to the "Project Name" column If Not Intersect(target, Range("A:A")) Is Nothing Then If target.Count 1 Then Exit Function Else With target Select Case .Value Case Is = "CA" Rows(target.Row & ":" & target.Row).Select Selection.Font.ColorIndex = 45 MsgBox "color is set to: " & Selection.Font.ColorIndex Case Is = "CD" ... End Select End With End If End If End Function again, thanks in advance, Mark "Norman Jones" wrote: Hi Mark, As indicated the code works for me. Your response: tried that... still nothing adds little that might be used futher to assist you, especially as you provide no indication of your experience when you implemented my suggestion: Have you tried setting break points and stepping through the code? --- Regards. Norman |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to update row format
On Jun 2, 1:02*pm, Mark Kubicki
wrote: sorry about that (by now I know I should be more explicit I did set break points, and I don't see anything unusual. * It steps thru each line without issue... *(it could be there is "something" going on, but, being self-tasught, I'm not certain what else to look for. *This is virtually the only code in the workbook. *In it's reduced version, this is the entire function: Function UpdateRowColor(target As Range) 'This section effects changes to the "Project Name" column * * If Not Intersect(target, Range("A:A")) Is Nothing Then * * If target.Count 1 Then * * * * * * Exit Function * * Else * * * * * * With target * * * * * * * * Select Case .Value * * * * * * * * * * Case Is = "CA" * * * * * * * * * * * * * * * * * * Rows(target.Row & ":" & target.Row).Select * * * * * * * * * * * * * * * * * * Selection.Font.ColorIndex = 45 * * * * * * * * * * * * * * * * * * MsgBox "color is set to: " & Selection.Font.ColorIndex * * * * * * * * * * Case Is = "CD" * * * * * * * * * * * * * * * * * * *... * * * * * * * * End Select * * * * * * End With * * End If * * End If End Function again, thanks in advance, Mark "Norman Jones" wrote: Hi Mark, As indicated the code works for me. Your response: tried that... still nothing adds little that might be used futher to assist you, especially as you provide no indication of your experience when you implemented my suggestion: Have you tried setting break points and stepping through the code? --- Regards. Norman- Hide quoted text - - Show quoted text - Mark, I can see no reason why this shouldn't be working. Have you tried any other code just to see if something may be buggy in this particular workbook? Try doing something like: Sub Test() MsgBox "Hello World" End Sub See if that runs. It certainly should, but if it doesn't, something may be wrong with the workbook as a whole. Try copying the data in the workbook over to a new one and then place the code in there and give it a shot. As I stated previously, the below code works for me without a problem. Private Sub Worksheet_Change(ByVal target As Range) Application.Run UpdateRowColor(target) End Sub Function UpdateRowColor(target As Range) 'This section effects changes to the "Project Phase" column If Not Intersect(target, Range("A:A")) Is Nothing Then If target.Count 1 Then Exit Function Else With target Select Case .Value Case Is = "CA" Rows(.Row).Font.ColorIndex = 45 Case Is = "CD" Rows(.Row).Font.ColorIndex = 5 End Select End With End If End If End Function |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to update row format
Hi Mark,
As the code works for me and also worked for JW, It would appear likely that the problem is particular to your environment \ workbook. If you would like me to look at a sample problematic workbook, you may send it to me: norman_jones@NOSPAMbtconnectDOTcom (Delete "NOSPAM" and replace "DOT" with a full stop [period] ) --- Regards. Norman "Mark Kubicki" wrote in message ... sorry about that (by now I know I should be more explicit I did set break points, and I don't see anything unusual. It steps thru each line without issue... (it could be there is "something" going on, but, being self-tasught, I'm not certain what else to look for. This is virtually the only code in the workbook. In it's reduced version, this is the entire function: Function UpdateRowColor(target As Range) 'This section effects changes to the "Project Name" column If Not Intersect(target, Range("A:A")) Is Nothing Then If target.Count 1 Then Exit Function Else With target Select Case .Value Case Is = "CA" Rows(target.Row & ":" & target.Row).Select Selection.Font.ColorIndex = 45 MsgBox "color is set to: " & Selection.Font.ColorIndex Case Is = "CD" ... End Select End With End If End If End Function again, thanks in advance, Mark "Norman Jones" wrote: Hi Mark, As indicated the code works for me. Your response: tried that... still nothing adds little that might be used futher to assist you, especially as you provide no indication of your experience when you implemented my suggestion: Have you tried setting break points and stepping through the code? --- Regards. Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
update code | Excel Discussion (Misc queries) | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
vba code...... on update........... | Excel Programming | |||
How can I update VBA code in a module that is running my VBA code? | Excel Programming | |||
code to update a macro | Excel Programming |