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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Is this an event routine? Double-Click? What is triggering the code?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
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.:)
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
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
 

i8ig

Board Regular
Joined
Jul 17, 2007
Messages
122
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
 

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,079
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. MacOS
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
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
A full explanation in the first post might have saved a lot of time.
 

Forum statistics

Threads
1,181,054
Messages
5,927,855
Members
436,573
Latest member
CMR237

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