ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Help with events (https://www.excelbanter.com/excel-programming/385165-help-events.html)

A1pro
Help with events
 
I 've got this macro (code below) that automatically does colours the row
when you type something on column 12.

I want it to be able to modify column 12 and then for all the colouring to
take place. At the moment it will not let me add anything. It will simply
select the row and colour it.

Any ideas how to do this, i.e. add data and then have the macro colour the
row?

TIA



Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

' Auto Data update macro

On Error Resume Next


Application.EnableEvents = False

If Target.Column = 12 Then

ThisRow = Target.Row

If (CLng(Target.Value) - CLng(Cells(1, 3))) = 15 Then

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With

ElseIf (CLng(Target.Value) - CLng(Cells(1, 3))) = 8 Then

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 46
.Pattern = xlSolid
End With

ElseIf (CLng(Target.Value) - CLng(Cells(1, 3))) = 1 Then

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With

ElseIf CLng(Target.Value) = 0 Then

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 0
.Pattern = xlSolid
End With

Else

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With


End If

Application.EnableEvents = True
end sub

Tom Ogilvy
Help with events
 
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

' Auto Data update macro

On Error Resume Next


Application.EnableEvents = False

If Target.Column = 12 Then

ThisRow = Target.Row

If (CLng(Target.Value) - CLng(Cells(1, 3))) = 15 Then

With Rows([ThisRow]).Interior
.ColorIndex = 4
.Pattern = xlSolid
End With

ElseIf (CLng(Target.Value) - CLng(Cells(1, 3))) = 8 Then

With Rows([ThisRow]).Interior
.ColorIndex = 46
.Pattern = xlSolid
End With

ElseIf (CLng(Target.Value) - CLng(Cells(1, 3))) = 1 Then

Rows([ThisRow]).Interior
.ColorIndex = 3
.Pattern = xlSolid
End With

ElseIf CLng(Target.Value) = 0 Then

Rows([ThisRow]).Interior
.ColorIndex = 0
.Pattern = xlSolid
End With

Else

Rows([ThisRow]).Interior
.ColorIndex = 1
.Pattern = xlSolid
End With


End If

Application.EnableEvents = True
end sub

--
Regards,
Tom Ogilvy

"A1pro" wrote:

I 've got this macro (code below) that automatically does colours the row
when you type something on column 12.

I want it to be able to modify column 12 and then for all the colouring to
take place. At the moment it will not let me add anything. It will simply
select the row and colour it.

Any ideas how to do this, i.e. add data and then have the macro colour the
row?

TIA



Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

' Auto Data update macro

On Error Resume Next


Application.EnableEvents = False

If Target.Column = 12 Then

ThisRow = Target.Row

If (CLng(Target.Value) - CLng(Cells(1, 3))) = 15 Then

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With

ElseIf (CLng(Target.Value) - CLng(Cells(1, 3))) = 8 Then

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 46
.Pattern = xlSolid
End With

ElseIf (CLng(Target.Value) - CLng(Cells(1, 3))) = 1 Then

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With

ElseIf CLng(Target.Value) = 0 Then

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 0
.Pattern = xlSolid
End With

Else

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With


End If

Application.EnableEvents = True
end sub

A1pro
Help with events
 
Thanks, that almost works.

The problem is that once I update the cell it does not colour the row. I
need to go back to the cell and click enter.

any ideas to get around this and have the cell automatically update once the
change in the cell content has been made?

TIA

"Tom Ogilvy" wrote:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

' Auto Data update macro

On Error Resume Next


Application.EnableEvents = False

If Target.Column = 12 Then

ThisRow = Target.Row

If (CLng(Target.Value) - CLng(Cells(1, 3))) = 15 Then

With Rows([ThisRow]).Interior
.ColorIndex = 4
.Pattern = xlSolid
End With

ElseIf (CLng(Target.Value) - CLng(Cells(1, 3))) = 8 Then

With Rows([ThisRow]).Interior
.ColorIndex = 46
.Pattern = xlSolid
End With

ElseIf (CLng(Target.Value) - CLng(Cells(1, 3))) = 1 Then

Rows([ThisRow]).Interior
.ColorIndex = 3
.Pattern = xlSolid
End With

ElseIf CLng(Target.Value) = 0 Then

Rows([ThisRow]).Interior
.ColorIndex = 0
.Pattern = xlSolid
End With

Else

Rows([ThisRow]).Interior
.ColorIndex = 1
.Pattern = xlSolid
End With


End If

Application.EnableEvents = True
end sub

--
Regards,
Tom Ogilvy

"A1pro" wrote:

I 've got this macro (code below) that automatically does colours the row
when you type something on column 12.

I want it to be able to modify column 12 and then for all the colouring to
take place. At the moment it will not let me add anything. It will simply
select the row and colour it.

Any ideas how to do this, i.e. add data and then have the macro colour the
row?

TIA



Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

' Auto Data update macro

On Error Resume Next


Application.EnableEvents = False

If Target.Column = 12 Then

ThisRow = Target.Row

If (CLng(Target.Value) - CLng(Cells(1, 3))) = 15 Then

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With

ElseIf (CLng(Target.Value) - CLng(Cells(1, 3))) = 8 Then

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 46
.Pattern = xlSolid
End With

ElseIf (CLng(Target.Value) - CLng(Cells(1, 3))) = 1 Then

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With

ElseIf CLng(Target.Value) = 0 Then

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 0
.Pattern = xlSolid
End With

Else

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With


End If

Application.EnableEvents = True
end sub

Tom Ogilvy
Help with events
 
Use the Change event instead of the selectionchange event:

' first line is changed:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

' Auto Data update macro

On Error Resume Next


Application.EnableEvents = False

If Target.Column = 12 Then

ThisRow = Target.Row

If (CLng(Target.Value) - CLng(Cells(1, 3))) = 15 Then

With Rows([ThisRow]).Interior
.ColorIndex = 4
.Pattern = xlSolid
End With

ElseIf (CLng(Target.Value) - CLng(Cells(1, 3))) = 8 Then

With Rows([ThisRow]).Interior
.ColorIndex = 46
.Pattern = xlSolid
End With

ElseIf (CLng(Target.Value) - CLng(Cells(1, 3))) = 1 Then

Rows([ThisRow]).Interior
.ColorIndex = 3
.Pattern = xlSolid
End With

ElseIf CLng(Target.Value) = 0 Then

Rows([ThisRow]).Interior
.ColorIndex = 0
.Pattern = xlSolid
End With

Else

Rows([ThisRow]).Interior
.ColorIndex = 1
.Pattern = xlSolid
End With


End If

Application.EnableEvents = True
end sub



"A1pro" wrote:

Thanks, that almost works.

The problem is that once I update the cell it does not colour the row. I
need to go back to the cell and click enter.

any ideas to get around this and have the cell automatically update once the
change in the cell content has been made?

TIA

"Tom Ogilvy" wrote:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

' Auto Data update macro

On Error Resume Next


Application.EnableEvents = False

If Target.Column = 12 Then

ThisRow = Target.Row

If (CLng(Target.Value) - CLng(Cells(1, 3))) = 15 Then

With Rows([ThisRow]).Interior
.ColorIndex = 4
.Pattern = xlSolid
End With

ElseIf (CLng(Target.Value) - CLng(Cells(1, 3))) = 8 Then

With Rows([ThisRow]).Interior
.ColorIndex = 46
.Pattern = xlSolid
End With

ElseIf (CLng(Target.Value) - CLng(Cells(1, 3))) = 1 Then

Rows([ThisRow]).Interior
.ColorIndex = 3
.Pattern = xlSolid
End With

ElseIf CLng(Target.Value) = 0 Then

Rows([ThisRow]).Interior
.ColorIndex = 0
.Pattern = xlSolid
End With

Else

Rows([ThisRow]).Interior
.ColorIndex = 1
.Pattern = xlSolid
End With


End If

Application.EnableEvents = True
end sub

--
Regards,
Tom Ogilvy

"A1pro" wrote:

I 've got this macro (code below) that automatically does colours the row
when you type something on column 12.

I want it to be able to modify column 12 and then for all the colouring to
take place. At the moment it will not let me add anything. It will simply
select the row and colour it.

Any ideas how to do this, i.e. add data and then have the macro colour the
row?

TIA



Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

' Auto Data update macro

On Error Resume Next


Application.EnableEvents = False

If Target.Column = 12 Then

ThisRow = Target.Row

If (CLng(Target.Value) - CLng(Cells(1, 3))) = 15 Then

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With

ElseIf (CLng(Target.Value) - CLng(Cells(1, 3))) = 8 Then

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 46
.Pattern = xlSolid
End With

ElseIf (CLng(Target.Value) - CLng(Cells(1, 3))) = 1 Then

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With

ElseIf CLng(Target.Value) = 0 Then

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 0
.Pattern = xlSolid
End With

Else

Rows([ThisRow]).Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With


End If

Application.EnableEvents = True
end sub

A1pro
Help with events
 
Thanks that works great

in order to change the font colour of the whole row I've had to add

With Rows([ThisRow]).Interior
.ColorIndex = 46
.Pattern = xlSolid
End With

Rows([ThisRow]).Font.ColorIndex = 1 ' ensures that font is black

as adding .font.colorindex = 1 or .font.color = 1 to the With statement
would not work.

any ideas why?

The way I do it works, but it just doesn't seem too elegant

Thanks for your time anyway

Tom Ogilvy
Help with events
 
No, Font isn't a child of Interior. You would have to set it up slightly
differently

With Rows([ThisRow])
.Font.ColorIndex = 1
With .Interior
.ColorIndex = 46
.Pattern = xlSolid
End With
End With

if you wanted to still use With.

--
Regards,
Tom Ogilvy




"A1pro" wrote in message
...
Thanks that works great

in order to change the font colour of the whole row I've had to add

With Rows([ThisRow]).Interior
.ColorIndex = 46
.Pattern = xlSolid
End With

Rows([ThisRow]).Font.ColorIndex = 1 ' ensures that font is black

as adding .font.colorindex = 1 or .font.color = 1 to the With statement
would not work.

any ideas why?

The way I do it works, but it just doesn't seem too elegant

Thanks for your time anyway



A1pro
Help with events
 
Cool that works

thanks

"Tom Ogilvy" wrote:

No, Font isn't a child of Interior. You would have to set it up slightly
differently

With Rows([ThisRow])
.Font.ColorIndex = 1
With .Interior
.ColorIndex = 46
.Pattern = xlSolid
End With
End With

if you wanted to still use With.

--
Regards,
Tom Ogilvy




"A1pro" wrote in message
...
Thanks that works great

in order to change the font colour of the whole row I've had to add

With Rows([ThisRow]).Interior
.ColorIndex = 46
.Pattern = xlSolid
End With

Rows([ThisRow]).Font.ColorIndex = 1 ' ensures that font is black

as adding .font.colorindex = 1 or .font.color = 1 to the With statement
would not work.

any ideas why?

The way I do it works, but it just doesn't seem too elegant

Thanks for your time anyway





All times are GMT +1. The time now is 09:00 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com