VBA code to update row format

[Mark Kubicki]

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

 

[JW]

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

 

[Norman Jones]

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.

 

[Mark Kubicki]

HMPFT!?... makes sense, but still nothing

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

 

[Mark Kubicki]

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]

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:

 

[Mark Kubicki]

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

 

[JW]

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








- 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

 

[Norman Jones]

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] )