Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Another VBA question

  Asked By: Lorenzo    Date: Dec 28    Category: MS Office    Views: 844
  

Below is my first attempt at a VBA method to change colors. Based on
the text value of H1, I would like for it to choose the colors of the
background and text. The ???? is what I definitely do not know. What do
I need to do that will choose the color of the text?

According my Excel book I shouldn't use "Range("C3")" but I should use
"Range.Cell(C3).Cell(C3)" instead. That doesn't work.

I have other color schemes planned but it would be too long to include
them all here. In interest of brevity and keeping an email short, I
trimmed them out.

Is the below simple enough for some kind soul to correct my error? I
tried recording macros, but when I record a macro, I never get a "Select
Case" type macro.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("H1")) Is Nothing Then
Select Case Target
Case "Warning"
Range("C3").Interior.ColorIndex = 3 ' red back
Range("C3").?????? = 2 'white text
Range("C4").Interior.ColorIndex = 2 'white back
Range("C4").?????? = 3 'Red text
Case "Help"
Range("C3").Interior.ColorIndex = 4 ' Green back
Range("C3").?????? = 1 ' Black text
Range("C4").Interior.ColorIndex = 1 ' Green back
Range("C4").?????? = 4 ' Black text
Case Else 'white out everything
Range("C3").Interior.ColorIndex = 2 ' WHITE back
Range("C3").?????? = 2 ' WHITE text
Range("C4").Interior.ColorIndex = 2 ' WHITE back
Range("C4").?????? = 2 ' WHITE text show
nothing!
End Select
End If

End Sub

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Robin Bailey     Answered On: Dec 28


OK, there's a couple of things you should be aware of.
If you use the line:
"If Not Intersect(Target, Range("H1")) Is Nothing Then"
then if a user, say deletes several cells including H1, then your change  event
will error  out because you're testing a target  RANGE (array) against a single
value.
It would be better to test the target.address against the specific cell in
question.
Also, learn how to use the VBA debugger. By using Bookmarks, Watches, and
Debug.Print
you can step through your program and see what values it's using to determine
program flow.
Using this, you'll find that the Target.Address being passed to the change event
is "$H$1".
Not "H1".

Next. Use the macro  recorder and select and change the cells to what you want
them to look like.
You get something like:

Range("C3").Select
Selection.Interior.ColorIndex = 3
Selection.Font.ColorIndex = 2

Range("C4").Select
Selection.Interior.ColorIndex = 2
Selection.Font.ColorIndex = 3

Now, it's possible to combine the Range().Select and the following (2)
Selections, so
they read:
Range("C3").Interior.ColorIndex = 3
Range("C3").Font.ColorIndex = 2
That way, you don't have to change your "focus".

Next. Your case  statement.
Keep in mind that it will be case sensitive.
I prefer to force the values to uppercase or lowercase, that way, if the user
were to type
"WARNING" instead of "Warning", the script would still work.

Using this, you end up with:
'---------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Address = "$H$1") Then
Select Case LCase(Target.Value)
Case "warning"
Range("C3").Interior.ColorIndex = 3 ' red  back
Range("C3").Font.ColorIndex = 2 'white text
Range("C4").Interior.ColorIndex = 2 'white back
Range("C4").Font.ColorIndex = 3 'Red text
Case "help"
Range("C3").Interior.ColorIndex = 4 ' Green back
Range("C3").Font.ColorIndex = 1 ' Black text
Range("C4").Interior.ColorIndex = 4 ' Green back
Range("C4").Font.ColorIndex = 1 ' Black text
Case Else 'white out everything
Range("C3").Interior.ColorIndex = 2 ' white  back
Range("C3").Font.ColorIndex = 2 ' WHITE text
Range("C4").Interior.ColorIndex = 2 ' WHITE back
Range("C4").Font.ColorIndex = 2 ' WHITE text  show
'nothing!
End Select
End If
End Sub

 
Answer #2    Answered By: Oscar Evans     Answered On: Dec 28

I HOPE that you are mis-reading the book. :-)

Range.Cell(C3).Cell(C3) is bad syntax.

Range("C3") is perfectly reasonable for a range that you already know as a
string.

You can create a range using two parameters, which represent top left and
bottom right of the area. Either or both of these could be Cells calls.
(But Cells calls have two parameters, not one.) So

Range(Cells(3, 3), Cells(3, 3))

or

Range(Cells(3, "C"), Cells(3, "C"))

will do the same thing, but are certainly not more "correct" and are
definitely less desirable.

If you have the range as a string, use Range(string). If you need to use
variables to supply row or column numbers, then by all means use Cells
calls. To describe a single cell, you use a Cells call without a Range
call; to describe a rectangular area, you can certainly put a couple of
Cells calls into a Range call.

 
Didn't find what you were looking for? Find more on Another VBA question Or get search suggestion and latest updates.




Tagged: