Modifying CASE function in VBA code [Closed]

Report
Posts
1
Registration date
Friday August 10, 2018
Status
Member
Last seen
August 10, 2018
-
Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
-
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






Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
440
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
Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
440
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
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!
Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
440
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
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!
Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
440
It is hard to judge, when someone asks for a solution, that the Peter Principle is in effect. The same goes for homework, so I'm just focussing on providing solutions.