Pass a cell address - Modify interior font

[CarlS]

There is probably a real simple way to solve this but I am just now learning
VBA for Excel.

Given the Sub Worksheet_Change I can work on the range(myproject) in one of
the macros. Why can't I pass a single cell to another common macro to change
the colors? I'd like to do this to avoid repeating the same commands for
each myProject

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 2 Then
Set myProject = Range("rProject1")
Project1 (myProject)

ElseIf Target.Column = 3 Then
Set myProject = Range("rProject2")
Project2 (myProject)

'<<Repeats for 10 projects>>
End If
End Sub

Sub Project1(myProject)
'Search through Project 1 and see if enough roles have been performed to
complete the project
myval = 0
For Each c In myProject
If c > "" Then
myval = myval + 1
End If
Next c

Set colorcell = Range("Pnum1")
If myval = 3 Then
ColormeGreen (colorcell)
Else
If myval > 3 Then
ColormeOrange (colorcell)
Else
'This works, the two above do not. Pnum1 is defined on the worksheet as cell
B2
With Range("B2")
.Font.ColorIndex = 1
.Interior.ColorIndex = 0
End With
End If
End If

End Sub

Sub ColormeGreen(colorcell)
For Each c In colorcell
.Font.ColorIndex = 3
.Interior.ColorIndex = 4
.Interior.Pattern = xlSolid
Next c
End Sub

 

[RyanH]

First I think you should change all your If...Then...ElseIf statements to the
Select Case statement. The Select Case statement is much more efficient.
Then declare you Range"myProject" outside the Sub, like below. This will
allow the myProject Range variable available to all the Procedures in the
Worksheet Module.

Option Explicit

Dim myProject As Range

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Select Case Target.Column

Case Is = 2
Set myProject = Range("rProject1")
Project1 (myProject)

Case Is = 3
Set myProject = Range("rProject2")
Project2 (myProject)

'<<Repeats for 10 projects>>

End Select

End Sub

Hope this helps!

 

[CarlS]

Thanks Ryan, I didn't know VBA had a CASE statement. The Option Explicit
caused problems from the get-go so I think I'll leave that out, for now
anyway. However, declaring myproject gives me an idea on troubleshooting the
problem with passing ranges between the non Worksheet_ macros. Maybe I need
to put something in the declarations there. It didnt seem to be a problem at
the Worksheet_ level but looks worth a shot.

 

[RyanH]

The Option Explicit is highly recommended. This will force you to delare
your variables which in turn will speed up your code, make sure there are not
misspelled vairiables, and basically make your code much more accurate and
efficient.

I hope this helps! If so , please click "Yes" below.
--
Cheers,
Ryan

Thanks Ryan, I didn't know VBA had a CASE statement. The Option Explicit
caused problems from the get-go so I think I'll leave that out, for now
anyway. However, declaring myproject gives me an idea on troubleshooting the
problem with passing ranges between the non Worksheet_ macros. Maybe I need
to put something in the declarations there. It didnt seem to be a problem at
the Worksheet_ level but looks worth a shot.

 

[Rick Rothstein \(MVP - VB\)]

I would like to second what Ryan has said here and call out special
attention to "[it will] make sure there are not misspelled variable
[names]". This reason cannot be over emphasized. Consider the following
scenario... you create a variable on the fly (that is, without using a Dim
statement) named CellVal and later on, thinking you were typing the same
name, accidentally typed CellVa1. What is different about the two names? The
last character... in the first name it is an 'l' (el); in the second name it
is a 1 (one). Or consider this variable name... Row01 (that is a zero) but
later on you accidentally type it as RowO1. Both of these 'accidental'
misspellings will be very, very hard to spot when you try and figure out why
your code isn't working. Or, WORSE yet, your code gives no sign of working
incorrectly... your calculated values for CellVal or Row01 simply end up
with the wrong values in them and your program generates incorrect results
that your users assume are correct. If you use Option Explicit, it will flag
the misspelled variable names for you and prevent the above disasters. Use
the Option Explicit statement... trust me, it is worth the effort.

Rick

The Option Explicit is highly recommended. This will force you to delare
your variables which in turn will speed up your code, make sure there are
not
misspelled vairiables, and basically make your code much more accurate and
efficient.

I hope this helps! If so , please click "Yes" below.