Modifying CASE function in VBA code

Closed

sell1 Posts 1 Registration date Friday August 10, 2018 Status Member Last seen August 10, 2018 - Updated on Aug 10, 2018 at 04:56 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Aug 23, 2018 at 11:31 AM

Hi hoping someone out there can help. I'm reasonably new to VBA code and was hoping to modify a CASE reference code to modify cell colour based on another cells content rather than cell text.

Here is the current section of relevant code;

Select Case Cl
Case "John"
dCell.Interior.ColorIndex = 27
Case "Peter"
dCell.Interior.ColorIndex = 32
Case "Anthony"
dCell.Interior.ColorIndex = 4
Case "Paul"
dCell.Interior.ColorIndex = 8
Case "Nicole"
dCell.Interior.ColorIndex = 34
Case "Carly"
dCell.Interior.ColorIndex = 3

The statements in the code above currently function well just want to replace text ie. "John" with a cell location "A3".Thanks in advance for any assistance.

Modifying CASE function in VBA code
Vba case like - Guide
Battery reset code - Guide
Samsung volume increase code - Guide
Cs 1.6 code - Guide
How to get whatsapp verification code online - Guide

2 responses

Answer 1 / 2

Blocked Profile
Updated on Aug 10, 2018 at 04:15 PM

Try this:


CellA3 = ThisWorkbook.Worksheets("Sheet1").Range("A3").value

Select Case CellA3

Case "here"
            do something
Case "there"
           do something
End Select

Answer 2 / 2

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Aug 14, 2018 at 11:44 AM

Hi Sell1,

Seems to me that the Select CASE method is not the right one for you, since your suggestion doesn't make sense.

Select Case Cl
Case "John"
dCell.Interior.ColorIndex = 27

Means, look at the value CI, when it is "John", then change dCell color.
Since a value can never be the same as a location, it doesn't make sense to change "John" into Range("A3").

Could you explain what you are trying to achieve?

Best regards,
Trowa

Blocked Profile
Aug 14, 2018 at 04:44 PM

I wasn't trying to re-engineer his solution, but trying to answer his request. I made it as generic as I could, seeing the flaw in the request. Be careful what you ask for! I will answer literally!

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Aug 16, 2018 at 11:08 AM

And I'm trying to figure out what OP wants, since it can be hard to figure out what structure to use, when you are not aware of which ones are out there.

What is the point of answering literally when you know OP is not being helped by it?

To each their own method I guess.

Blocked Profile
Aug 17, 2018 at 04:51 PM

Perhaps the OP saw the error in his logic, once we placed the code he asked for in front of him? Then it is more likely to stick, because HE learned it, and didn't just cut and paste like a script kiddy!

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Aug 21, 2018 at 11:55 AM

Good point. I just think (guess) that most posters aren't here to learn or get proficient at what they are asking about. So I (try to) provide a solution and when they want to know/learn more, because they are interested, they can ask for it.

Blocked Profile
Aug 21, 2018 at 04:40 PM

Or, worse yet, they are at a level of incompetence, and shouldn't be performing the work that they have "convinced" their bosses they are capable of! Peter Principle 101! Promoted to the point of incompetence!

Similar discussions

How can get a code game for Nokia 105

unlock codes for nokia

Similar discussions

Subscribe To Our Newsletter!