Making a macro run

i8ig

Board Regular
Joined
Jul 17, 2007
Messages
122
I have written the following macro but cant get it to do anything, any suggestions?


Private Sub StevesWorkbook(ByVal Target As Range)

If Target.Colunm = 1 Then
If Target.Value = "Med" Then
Rows(Target.Row).Interior.ColorIndex = 4
Cells(Target.Row, 11) = "Q="","",Q-5"
Else
If Target.Value = "Tasc" Then
Rows(Target.Row).Interior.ColorIndex = 5
Cells(Target.Row, 11) = "M="","",M-2"
Else
If Target.Value = "Nbar" Then
Rows(Target.Row).Interior.ColorIndex = x1ColorIndexNone
Cells(Target.Row, 10) = "M="","",M-2"
Cells(Target.Row, 12) = "O="","",O-3"
Cells(Target.Row, 14) = "Q="","",Q-5"
Else
Rows(Target.Row).Interior.ColorIndex = xlColorIndexNone
End If
End If
End If
End If
End Sub







Sub Steves()

End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
sounds like you want to run this code when user changes someting on the Sheet. Paste it into the SHEET module..

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Colunm = 1 Then
    If Target.Value = "Med" Then
        Rows(Target.Row).Interior.ColorIndex = 4
        Cells(Target.Row, 11) = "Q="","",Q-5"
    Else
        If Target.Value = "Tasc" Then
            Rows(Target.Row).Interior.ColorIndex = 5
            Cells(Target.Row, 11) = "M="","",M-2"
        Else
            If Target.Value = "Nbar" Then
                Rows(Target.Row).Interior.ColorIndex = x1ColorIndexNone
                Cells(Target.Row, 10) = "M="","",M-2"
                Cells(Target.Row, 12) = "O="","",O-3"
                Cells(Target.Row, 14) = "Q="","",Q-5"
            Else
                Rows(Target.Row).Interior.ColorIndex = xlColorIndexNone
            End If
        End If
    End If
End If
End Sub
 

i8ig

Board Regular
Joined
Jul 17, 2007
Messages
122
Good suggestion,
Did as suggested, received a Run Time Error '438': Object does not support this property or method

When pressing the 'debug' button the line: If Target.Column = 1 Then is highlighted in yellow and I have no idea what to do

Suggestions?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
ahh, because you have column Spelled incorrectly in the code...
 

i8ig

Board Regular
Joined
Jul 17, 2007
Messages
122
Thanks, however with the following Macro:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Value = "Med" Then
Rows(Target.Row).Interior.ColorIndex = 3
Cells(Target.Row, 11) = "Q="","",Q-5"
Else
If Target.Value = "Tasc" Then
Rows(Target.Row).Interior.ColorIndex = 8
Cells(Target.Row, 11) = "M="","",M-2"
Else
If Target.Value = "Nbar" Then
Rows(Target.Row).Interior.ColorIndex = x1ColorIndexNone
Cells(Target.Row, 10) = "M="","",M-2"
Cells(Target.Row, 12) = "O="","",O-3"
Cells(Target.Row, 14) = "Q="","",Q-5"
Else
Rows(Target.Row).Interior.ColorIndex = xlColorIndexNone
End If
End If
End If
End If
End Sub

This is what shows up on the spreadsheet:

File AAA Case Claim Insured's Claim S. F. Date Selected Rep To EXC Hearing
Type Number Insured Number Rep Rep Attorney Received Mediator CK Date CK Daphne CK Date CK Date
Med Q=",",Q-5(Col 11)
Tasc M=",",M-2(Col)
Nbar M=",",M-2(Col 10) O=",",O-3(Col 12) Q=",",Q-5(col 14)

This is what displays ( ) provided by me as a reference, the formula results does not display. Also the color of the lettering is green instead of black?
Any suggestions?
 

Forum statistics

Threads
1,181,372
Messages
5,929,565
Members
436,679
Latest member
helly123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top