Problem using a variable that contains text in VBA conditional formatting...

Hi all you Excel wizards; I am having a problem with a macro that refuses to do as its told! I have looked through other threads but can't find a solution that suits my issue...

I have a variable "Initials", that picks up a certain cell's contents; the cells contain guess what - initials in plain text - i.e. "BF"

When I use:

Please Login or Register  to view this content.

No cells that contain the text "BF" are formatted - when I then look at the conditional formatting rule in the spreadsheet for the range I see from the criteria that is picked up is Cell Value = BF - not "BF" (i.e. quotes are missing)

How do I wrap quotes around my variable (or do something else) so that it recognizes the text in the cell my variable is allotted?

I am sure I have overlooked something simple; however, your speedy response will be gratefully received - should you require further information please respond - thx...

try this,

Please Login or Register  to view this content.

If you want to check for the text value 'BF' then the formula would be ="BF".

In code to get the "" around the value from Initials you need to do it like Xlbzines suggests.

Try testing it.

Please Login or Register  to view this content.

So your code should look like this.

Please Login or Register  to view this content.

Thx again for the quick reply - sadly this formats all empty cells because when I then look at the conditional formatting rule in the spreadsheet for the range I see from the rule that the criteria it has picked up is Cell Value = ""

???

Have you checked Initials has a value?

Originally Posted by TheSaint0001

Thx for a very quick reply, xlbiznes; however, my variable "Initials" is running through a "for-next" loop, picking up cell contents in sequence in one worksheet and (attempting to) format ranges in different worksheets

Users have access to an input worksheet where they insert their names and initials - this is the worksheet my for-next loop is running through... consequently the cell contents are plain text...

What i infer from this is that initials got populated with BF after the for loop , it could differ based on the cell contents! am i right about this .

Hi - answering Norie:

I tried the code in your original post but that returned an error so I tried this amalgam:

Columns("E:H").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""" & Initials & """"

and put a Stop command in to check the value of Initials:

Value for Initials = "Empty" as previously described

Getting more baffled by the moment... Answering XLbiznes next...

Hi - answering xlbiznes:

The variable Initials becomes populated with BF during the first loop of the for-next statement

BF could literally be any string of text depending on what the user inserts as his/her initials

The for-next loop runs sequentially down rows of different personnel, picking up a different set of initials at each pass so I can highlight their initials (using conditional format) in a list of actions required to be performed by them

Hope this is clearer???

Can you post the rest of the code?

The variable has the required string after all your code is over and it needs to be enclosed in " so that the conditional formatting kicks in for that condition.

Can you try this :

Please Login or Register  to view this content.

Many, many thanks to both Norie and xlbiznes for sticking with me throughout this matter...

truly a eureka moment!!! The line of code from xlbiznes has worked like a dream!

I really don't know why VBA requires such a convoluted/strange/illogical method of wrapping quotes around things but I guess that using quotes themselves when they are used extensively elsewhere is fraught with complications - guess I will never understand it properly and that's why I'm here on this excellent Forum, receiving expert advice from friendly and patient fellow conspirators...

I am extremely grateful...

You are welcome & thank you for the reps.