Help with events

A

A1pro

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
 
T

Tom Ogilvy

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
 
A

A1pro

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 said:
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 said:
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
Click to expand...
Click to expand...
 
T

Tom Ogilvy

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
A

A1pro

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
 
T

Tom Ogilvy

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.
 
A

A1pro

Cool that works

thanks

Tom Ogilvy said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

function to colour cells 2
Format Data Series in a Pivot Chart 2
How to change the row number in macro code? 1
Scanning in reverse order 4
keep getting the error message - Compile error: Else without If 2
piece of code keeps failing.... 2
insert format after specific time in string. 0
Run macro on refresh data (pivot table) 0

Top