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

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

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

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

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

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

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

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

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