Another Object Error

[AZGJC]

Hi, I see there's someone having a similar problem in the thread down below, but unfortunately it's not helping me figure out what's going on with my code. I'm trying to conditionally format cells based on input values. I keep getting an object code required error when I run the following code which I was able to find on the net. I have put the code in the specific sheet, not the module. Any help would be appreciated.

Thanks,
Garrett


Sub Change_color()

'Selecting Color based on Actual Values
Dim icolor As Integer

If Not Intersect(Target, Range("C4:C37")) Is Nothing Then
Select Case Target
Case 0.98 To 1
icolor = 32
Case 0.95 To 0.9799
icolor = 34
Case 0.93 To 0.9499
icolor = 10
Case 0.9 To 0.9299
icolor = 27
Case 0 To 0.8999
icolor = 3
End Select
Target.Interior.ColorIndex = icolor

End If

End Sub

 

[SkipVought]

Hi,

How does Change_color get Target? It is undefined!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AZGJC]

Thanks Skip. Excuse my lack of knowledge, but I'm learning VBA on the fly. I previously tried defining target as a variable and it didn't work, but your nudge helped me look a little more and find the colorformat variable. Now I'm getting a problem with that last statement before END IF, if I remove it I get an invalid call argument. Is this where the code is attempting to define what icolor = 3 means?

 

[SkipVought]

"if I remove it I get an invalid call argument"

does not make sense.

What statement is the error on?

"Is this where the code is attempting to define what icolor = 3 means?"

icolor = 3 is just an expression. Three, happens to be the colorindex for RED.

There's nothing wrong with Target.Interior.ColorIndex = icolor as long as Target is a Range variable.

Again, you have not defined Target. If you are attempting to use Target from the Worksheet_Change event, then you have to pass Target as an ARGUMENT in your Change_color procedure, like...

Sub Change_color(Target as range)
'.....
end sub

and then call the procedure from the change event...

Private Sub Worksheet_Change(ByVal Target As Range)
  Change_color Target
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AZGJC]

Thanks again. It's not telling me where the error is happening, it's just giving me an invalid procedure and call arguement. The code is the same as my first post except I have defined the Target as a Range variable.

 

[SkipVought]

rather that explaining what you did or did not do, please post your code and identify the statment that errors with the error message.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}