Modifying CASE function in VBA code

Closed
Report
Posts
1
Registration date
Friday August 10, 2018
Status
Member
Last seen
August 10, 2018
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
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.

2 replies

Try this:

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

Select Case CellA3

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






0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
0
Blocked Profile
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!
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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.
0
Blocked Profile
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!
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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.
0
Blocked Profile
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!
0