Range & variable problem

Posted by Raj on July 17, 2001 8:13 AM

Hi,
I hope to explain this correctly.
I have 2 variables eg. rownumber and columnumber
The values of these variables changes according to the loop ( eg. For rownumber = 1 to 10
- do something -
For columnumber = 1 to 10
- do something - )
What I need to do is go to this range and have the range colors changed. I do this using VBA and the routine to change the cell color is easy. But I do not want to set the row and column position in routine for the range.
Eg. Range("A1").select
My problem is I do not want to fix it to eg. A1
Assuming the value of the variable rownumber =3 and columnnumber is 5, it should be at row 3 and column 5 or in other words C3.
How do I substitute 'A1' with the variables ? Do I use '&' and if yes, what is the syntax ?

Hope I have explained this well. Appreaciate your help and also want to say that this is an excellent site for Excel problems. Great job !!

Thanks & regards,
Raj

Posted by Russell on July 17, 2001 8:26 AM

Use "Cells":

Cells(Row,Column).Select
Selection.ColorIndex = ....

Hope this helps,

Russell

Posted by Raj on July 17, 2001 8:44 AM

Hi Russell,
I still have the problem. Now the error is 'Run-time Error 1004. Application defined or object defined error.

This is the code.Did I make any silly mistake ? Thanks for the help
--------------------------
Dim FirstRowNum As Long
Dim FirstNumber As Long
Dim SecondRowNum As Long
Dim SecondNumber As Long
Dim ThirdRowNum As Long
Sub GetNumber()
For FirstRowNum = 1 To 10
FirstNumber = Cells(FirstRowNum, 1)
For SecondRowNum = 1 To 10
SecondNumber = Cells(SecondRowNum, 2)
If FirstNumber = SecondNumber Then AddNum
test
Next SecondRowNum
Next FirstRowNum
End Sub

Sub AddNum()
ThirdRowNum = ThirdRowNum + 1
Cells(ThirdRowNum, 3) = FirstNumber
End Sub

Sub test()
'
' test Macro
' Macro recorded 7/16/2001 by Raj
'

'
Cells(FirstRowNum, SecondRowNum).Select

With Selection.Interior
.ColorIndex = 27
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub

Posted by Russell on July 17, 2001 9:00 AM

Well, your AddNum function won't know what firstnum or any of the other variables in your first function are. Can you tell me exactly what you are trying to do? test Macro Macro recorded 7/16/2001 by Raj ' With Selection.Interior .ColorIndex = 27 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With

Posted by Raj on July 17, 2001 9:19 AM

Russell,

Well basically this is only a test and if I succeed, I will work on something bigger. Just trying to understand the syntax and logic.

Basically I am comparing a set of numbers ( from A1 to A10 ) against another set of numbers ( B1 to B10 ). Should there be any numbers from B1:B10 that matches the numbers in A1:A10, I just want to select that cell ( must be in col B ) and change the color.That's all.

Cheers !

Posted by Russell on July 17, 2001 10:29 AM

Ok, so you are checking cell A1 against all the cells between B1 and B10 to see if it matches any of them, right? Then you're doing the same with A2? If so, this should work:

For intI = 1 to 10

CellOneValue = Cells(intI, 1).Value

For intJ = 1 to 10

If Cells(intJ, 2) = CellOneValue Then
' Change color
Cells(intI, 1).Select
' Selection.CHANGE COLOR HERE

Exit For ' You've matched, so you don't need to keep checking

End If

Next intJ

Next intI

Russell, Well basically this is only a test and if I succeed, I will work on something bigger. Just trying to understand the syntax and logic. Basically I am comparing a set of numbers ( from A1 to A10 ) against another set of numbers ( B1 to B10 ). Should there be any numbers from B1:B10 that matches the numbers in A1:A10, I just want to select that cell ( must be in col B ) and change the color.That's all. Cheers !

Posted by Russell on July 17, 2001 10:32 AM

One change

Change one line:

If Cells(intJ, 2).Value = CellOneValue Then Ok, so you are checking cell A1 against all the cells between B1 and B10 to see if it matches any of them, right? Then you're doing the same with A2? If so, this should work: For intI = 1 to 10 CellOneValue = Cells(intI, 1).Value For intJ = 1 to 10 If Cells(intJ, 2) = CellOneValue Then Change color

Posted by Raj on July 18, 2001 8:05 AM

It Works !! Thanks

Russell,

Cheers mate, it works perfectly. Now I need to include this in the bigger program.

As mentioned before you guys are great ! Who do you guys work for anyway - Ivan, Melc etc ?

Thanks.