Lost variable when using sub-routine or Function in Excel VBA

[FishermanFrank]

Hello, having difficulties in the syntax for a subroutine to highlight certain cells. The code compares values in cells. If cell values are equal, I need to highlight the cell. It appears that in the HighLiteCell Sub, the variable cr is lost and I'm getting an error message.

thanks for the help..


Public Sub CommandButton1_Click()
Dim cas, cre As long
Dim r, i, cr As Integer
r = 1
cr = 1
Do While Cells(cr, 3) <> Empty
cas = Cells(cr, 3).Value
Do While Cells(r, 6) <> Empty
cre = Cells(r, 6).Value
If cas = cre Then HighLiteCell
r = r + 1
Loop
r = 1
cr = cr + 1
Loop
End Sub

Sub HighLiteCell()
Cells(cr, 3).Interior.ColorIndex = 6
End Sub

 

[PHV]

...
If cas = cre Then HighLiteCell cr
...
Sub HighLiteCell(x As Long)
Cells(x, 3).Interior.ColorIndex = 6
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fumei]

The point being is that in

Sub HighLiteCell()
Cells([b]cr[/b], 3).Interior.ColorIndex = 6
End Sub

the Sub has no idea what cr is. The variable cr is not in Scope. The variable cr is ONLY in scope within the Sub CommandButton1_Click.

PH's code has x as a parameter, and that variable is therefore in scope.

HighLiteCell(x As Long) takes in a parameter x.

HighLiteCell cr passes cr as that parameter.

Look up Scope in Help. It is a crucial part of coding.

Gerry
My paintings and sculpture

 

[GlennUK]

Just curious, but why aren't you using Conditional Formatting? Or must this be in VBA?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Zathras]

Some observations:

If you had used "Option Explicit" as the first line in your code, the compiler would have told you that variable "cr" is undefined in Sub HighLiteCell()

The way you have defined your variables in Sub CommandButton1_Click(), variables "cas", "r", and "i" are Variants and not Long/Integer as you think. (You can view the Locals window while stepping thru the code to verify that.)

"r" and "cr" are used as row numbers. Row numbers should be defined as Long since it is possible to have more than 32,768 rows in a spreadsheet. While you may know that the size of the data will never be more than a few rows (in this case), it is a good habit to develop.