Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Help with events
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
do events? background events cereldine[_15_] Excel Programming 1 April 12th 06 01:06 PM
events? [email protected] Excel Discussion (Misc queries) 1 September 14th 05 03:26 PM
how do I log events bmarks Excel Programming 0 December 13th 04 07:33 PM
Events ojv[_2_] Excel Programming 3 October 22nd 04 01:59 PM
events Mark[_17_] Excel Programming 1 October 29th 03 12:56 PM


All times are GMT +1. The time now is 04:10 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement