I must be stupid

i8ig

Board Regular
Joined
Jul 17, 2007
Messages
122
Can someone suggest how to make this work?


Code:
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 7/28/2007 by i8ig
'

If Target.Column = 1 Then
   If Target.Value = "Med" Then
      Rows(Target.Row).Interior.ColorIndex = 4
      Range("H3").Select
      ActiveCell.FormulaR1C1 = "=IF(RC[3]="""","""",RC[3]-3)"
   Else
       If Target.Value = "Tasc" Then
          Rows("4:4").Interior.ColorIndex = 44
          Range("H4").Select
          ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
       Else
           If Target.Range = "NBAR" Then
              Range("J5").Select
              ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-5)"
              Range("I5").Select
              ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
              Range("H5").Select
              ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
           End If
       End If
   End If
End If
End Sub

I continue to receive an error '424' object required and I cant find it

EDIT: added code tags - Smitty
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Is this an event routine? Double-Click? What is triggering the code?
 
Upvote 0
I don't see Target defined anywhere in that code, or passed to the sub.

So basically it doesn't exist.:)

PS To help us help you it might help if you explained how the code should work.:)
 
Upvote 0
Is this what you mean/

Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column > 1 Then End
    On Error Resume Next
    Select Case Target.Value
        Case "Med"
            Target.EntireRow.Interior.ColorIndex = 4
            Range("H3").FormulaR1C1 = "=IF(RC[3]="""","""",RC[3]-3)"
        Case "Tasc"
            Rows("4:4").Interior.ColorIndex = 44
            Range("H4").FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
        Case "NBAR"
            Range("J5").FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-5)"
            Range("I5").FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
            Range("H5").FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
    End Select
    On Error GoTo 0
End Sub
 
Upvote 0
royUK, tried to use, no results.

Corrected 'IF Target.Range' to Target.Value, still does not work.

I did not understand about Target not being defined, I thought 1st line refered to the target as being Column 1, is this not correct?

I am attempting to use this on a spreadsheet where the various 'names' will be entered numerous times in various orders. Each time the 'name' is entered a date will be entered in one of 2 columns and I need the calculation to be preformed as they are listed for the various 'names'.

For example if 'Tasc' is entered in Column 1, i need the cell to turn red and cell 'H' to display the results of subtracting 2 from the value of the date in cell 'I'.

If I enter NBAR in Column 1, I need cell 'H' to subtract 2 for the value of the date in cell 'I', and display the results, then cell 'I' needs to s'ubtrace 2 from the value of cell 'j' and display the value in cell 'I' and then in cell 'J' needs to subtract 5 from the value of cell 'K' and display the results in cell 'J'.

Hope this helps
 
Upvote 0
Code:
Sub test()

 Set c = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
If c Is Nothing Then
    last = 1
Else
    last = c.Row
End If

    For x = 1 To last
    Target = Cells(x, 1).Value
    Select Case Target
        Case "Med"
            Cells(x, 1).EntireRow.Interior.ColorIndex = 4
            Range("H3").FormulaR1C1 = "=IF(RC[3]="""","""",RC[3]-3)"
        Case "Tasc"
            Rows("4:4").Interior.ColorIndex = 44
            Range("H4").FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
        Case "NBAR"
            Range("J5").FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-5)"
            Range("I5").FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
            Range("H5").FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
    End Select
    On Error GoTo 0
    Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

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
Back
Top