Format a Variable 1

[WaterSprite]

[Select Case aa13]
[Case vbYes]
[Cells(11, 2) = aa12 & aa36]
[Case vbNo]
[Cells(11, 2) = aa12 & ":No:" & aa38]
[End Select]

This code works just fine, it is from the help I received from a lot of you in the last couple of weeks, my Select Case question from 20 September 2010.

Now, for my next problem, or maybe wishful thinking.

aa12 = "Horn sounds?"
aa36 = "Yes"

I want aa36 to turn green if Select Case aa13 is VByes. If the "Yes" that is assigned to aa36 came from a cell, no problem. But, I just assigned it in the macro, and I cannot figure out how to format a variable.

 

[SkipVought]

hi,

Variables are not formatted, as in colors, etc. Ranges can be...

Select Case aa13
  Case vbYes
    Cells(11, 2) = [aa12] & [aa36]
    with [aa36]
      if UCase(.value) = "YES" then .interior.color = vbgreen
    end with
  Case vbNo
    Cells(11, 2) = [aa12] & ":No:" &  [aa38]
End Select

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[WaterSprite]

Thanks, just what I was hoping for. I will use the same idea and have the "NO" set in Red if it comes up. Thanks so much for the help.

 

[PHV]

Why not simply use conditional format ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Ditto to PHV,

in fact this entire routine could be done using native Excel functionality!

Which REALLY ought to be the primary objective when using Excel, in most circumstances.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[WaterSprite]

The reason I prefer to use code for the spreadsheets is that others use them and I have learned that if there are formula's in the individual cells, sooner or later someone is going to play with them. If I do everything in code, they can play all they want, but no permanent damage done.
And, they seem to run faster using code versus formula's in the cells. Code just seems better all the way around to me.

 

[fumei]

And, they seem to run faster using code versus formula's in the cells. Code just seems better all the way around to me. "

Do you have empirical evidence for this? Generally speaking native functions should run faster.


unknown

 

[firefytr]

Some general rules to keep in mind:

Worksheet functions always run faster in the worksheet as opposed to being used in VBA.
There is always someone who will 'tinker' with your information, including formulas AND [VBA] code.
Excel is NOT a secure environment.

Of course there are more, but pertaining to your question, these are important facts to realize.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci