RE: Variable variables and sub routines

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Don't think I got it eaxactly right. but this should help

Sub colourit(target As Range, Compare)
'Colour it


With target.Interior

Select Case Compare
Case Is >= aci2004
'Light orange
.ColorIndex = 3
.Pattern = xlSolid
Case Is >= adi2004
'Light orange
.ColorIndex = 45
.Pattern = xlSolid
Case Is >= aei2004
'Light orange
.ColorIndex = 6
.Pattern = xlSolid
Case Is >= afi2004
'Light orange
.ColorIndex = 36
.Pattern = xlSolid
End Select
abi2004.Interior.ColorIndex = xlNone
End With
End Sub
Sub colset()
For ColCount = 4 To 6
'Set ranges for colour up (round one)
Compare = Cells(5, ColCount)
Call colourit(Range(Cells(5, ColCount), Cells(27, ColCount)), _
Compare)
Call colourit(Cells(47, ColCount), Compare)
Call colourit(Cells(48, ColCount), Compare)
Call colourit(Cells(49, ColCount), Compare)
Call colourit(Cells(50, ColCount), Compare)
Next ColCount
End Sub

"Also" wrote:

(Excel 2000)
OK,Really wanted conditional formatting with 5 options but failing that have
created a Macro to do the colouring for me:

----------
Sub colourit()
'Colour it
If aai2004 >= aci2004 Then
abi2004.Select
With Selection.Interior
'Light orange
.ColorIndex = 3
.Pattern = xlSolid
End With
ElseIf Iaai2004 >= adi2004 Then
abi2004.Select
With Selection.Interior
'Light orange
.ColorIndex = 45
.Pattern = xlSolid
End With
ElseIf aai2004 >= aei2004 Then
abi2004.Select
With Selection.Interior
'Light orange
.ColorIndex = 6
.Pattern = xlSolid
End With
ElseIf aai2004 >= afi2004 Then
abi2004.Select
With Selection.Interior
'Light orange
.ColorIndex = 36
.Pattern = xlSolid
End With
Else: abi2004.Select
Selection.Interior.ColorIndex = xlNone
End If
End Sub
----------

However, I want to change the cell ranges each time so they are set using:
----------
Sub colset()
'Set ranges for colour up (round one)
Set aai2004 = Range("D5")
Set abi2004 = Range("D5,D27")
Set aci2004 = Range("D47")
Set adi2004 = Range("D48")
Set aei2004 = Range("D49")
Set afi2004 = Range("D50")
colourit
End Sub

----------
Of course it doesn't work as it can't read the variables...
And I can't set the globally as I want to set it the next time round as:
----------
Set aai2004 = Range("F5")
Set abi2004 = Range("F5,F27")
Set aci2004 = Range("F47")
Set adi2004 = Range("F48")
Set aei2004 = Range("F49")
Set afi2004 = Range("F50")
----------

I could do it if I understood loops to just increment two columns each time
(e.g. D to F) for 8 loops. But not sure how.
But as you can imagine, unless I try to tidy up the calls etc it's going to
become very large very quickly.
Any thoughts on how I could make this work?...
At the moment I am ending up coding in Word and using search and replace!

All help/comments/being called an idiot for long coding (and then a
suggestion how to change it) gratefully received.
.

Relevant Pages

  • Re: Validate Cell Value
    ... "Tim Zych" wrote: ... Private Sub Worksheet_Change(ByVal Target As Range) ... Workbook Compare - Excel data comparison utility ...
    (microsoft.public.excel.programming)
  • RE: Comparing data in two sheets with similar values
    ... Sub TestCompareWorksheets() ... ' compare two different worksheets in two different workbooks ... .Weight = xlHairline ...
    (microsoft.public.excel.worksheet.functions)
  • RE: Complex Copy
    ... Try the below macro which will create a copy of ws1 and work on it....Try ... Sub CompareSheets() ... Dim ws1 As Worksheet, ws2 As Worksheet ... If you mean to compare row by row then try the below macro which will work ...
    (microsoft.public.excel.programming)
  • Re: is it POSSIBLE to create a Database in excel using VBA...
    ... If a row is selected in the database and clicked - it should show all the ... As for using as a DataBase, a simple Compare sub is needed, the sub below is extended to show you an example of how to compare elements opposed to the complete value in a cell. ... GoTo Skip ' If more than 1 compare, add a 'Skip' sub to bypass code between success when comparing Cells and what you require as a result ...
    (microsoft.public.excel.programming)
  • Logic/Comparison problem.
    ... information from a PC and store it in an access database. ... trouble is if I compare it to strComputer which is equal to "." ... End Sub ...
    (microsoft.public.win32.programmer.wmi)