Change font color based on the ending text string (date) of a cell

Hello!

I have a range from Cells B2:E5. Within each cell, there is a text string with a bullet point. At the end of each text string, there is a date in the format (mm/dd) and the text string also includes these parenthesis. The text string will also sometimes either include the words "Green*", "Yellow*", or "Red*", AFTER the date or the text string could not contain any of these. However, if the text string does contain one of these words it will ONLY be one of them never two. What I want the macro to do is the following:

- If the string DOES NOT include (Green*, Yellow*, Red*), then I want this text to be GREY.
- If the string CONTAINS any kind of (Green*, Yellow*, Red*) AND the date within the string is today or prior, then I want this text to be RED.
- If the string CONTAINS any kind of (Green*, Yellow*, Red*) AND the date within the string is AFTER today (in the future), then I want this text to be BLACK.


However, there is a catch. Even though the text string can contain the words ("Green*", "Yellow*", or "Red*") I want every single one of these text string to remain their color in nature. Meaning even though the text string qualifies for a certain parameter for the string to be GREY I still want the text string that contains "Green*" to be the color green, and so on for Yellow or Red.

Please see the example workbook attached.

Hey Ollie,

Once again, your code worked beautifully within the example workbook . When I transferred the code to the target workbook, I received a "Type Mismatch" runtime error at the following code line (line 75):

datTargetDate = DateSerial(Year(Now()), CInt(Mid$(varBulletLine, intBracketFound + 1, 2)), CInt(Mid$(varBulletLine, intBracketFound + 4, 2)))

The only part of the code I edited was the Sheet name "CommSlide" and the range "D2:Z113"

Any thoughts?

Hey Ollie,

The coding is almost there... A few things I see that need to be fixed:

- Some text strings will have a range of dates (01/18-02/22)
* If this happens, and taking into account the parameters mentioned before:
1) If the string DOES NOT include (Green*, Yellow*, Red*), then I want this text to be GREY.
2) If the string CONTAINS any kind of (Green*, Yellow*, Red*) AND the date within the string is today or prior, then I want this text to be RED.
3) If the string CONTAINS any kind of (Green*, Yellow*, Red*) AND the date within the string is AFTER today (in the future), then I want this text to be BLACK.

* Then this text string is actually considered on-time (so should be black font) since the 2/22 is in the future and is after today's date.
* So, if the text string contains a date RANGE, use the END of the date range for the parameter.
** Currently the VBA doesn't take this into account, I believe your coding is looking at the 1st date, I aplogize as I failed to mention this earlier.

- Some of the text that contains the words, "Green*", "Yellow*", and "Red*" didn't maintain their color and are black. Not sure why..but there are lots of them that didnt maintain their true color in nature.
* I thought this might've been happening for all of the text that met the 3rd parameter, but this doesn't seem to be the case:
- For the text strings that meet the 3rd parameter, there are some text strings that kept the Green, Yellow, or Red color and then some that didn't. However, I don't see a pattern for this error.

- For the 1st parameter, I don't see any errors and this seems to be working exactly as it should.

I have made the example workbook much more similar to how the actual workbook looks like. This is about the range I work with. Instead of the words ("Green*", "Yellow*", and "Red*") maintaining their current color, lets add some code that just changes these words font color to their color in nature every time so there is never one that is missed.

Please let me know if you have any questions or concerns

I transfered to Target Workbook, subscript out of range line 113 in code:

strMonthPart = Trim$(Split(Split(strDatePart, "-")(1), "/")(0))

Okay. What is the code that I need to put before or after the code where I am receiving the error?

Hey Ollie,

Sorry I haven't responded..I have been out of town for the holiday weekend, but now back to work and hitting the ground running!

So I took a look at thet code and at the point where it errored out. I checked the cell that was giving the error. It appears that in one of the text strings, there are 2 different instances of the parenthesis. In this cell, the string reads like this:

• String has the following values in parenthesis (Infrastructure Deadline) (01/18) *Green

There is a step within my process within extracting the data that I believe I can get rid of the first set of parenthesis. I'll let you know what happens!

Hey Ollie!

You are right it has been awhile...I do apologize. I haven't been able to fix the error. But my boss asked me to work on a different assignment so I have somewhat put this on the back burner. I think it is an easy fix, I just have to find the time to find a work around. Can we keep this open for the mean time?

Brian