Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Lorenzo Lee   on Dec 28 In MS Office Category.

  
Question Answered By: Robin Bailey   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

Share: 

 

This Question has 1 more answer(s). View Complete Question Thread

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


Tagged: