Macro that will turn any text (bracketed) to red

Hello All,

I created two condition formats for the range of G1017:G4007 to turn certain text red.

First Condition format, if cell contains text "No EN Worker Category", works so no need to change this unless the macro will effect this.

The second Condition format is what I would like to be replaced with a macro. I want any text in (bracketed) to turn red and only the text in (brackets).

Attached excel sheet for your reference. EN BG Report (Master) v2.2.3.xlsm

In column M, I made a example of what the macro should do. In row 1037 and 1051 only the (bracketed) is red while the rest remains black.

Hope this was clear.

Thanks,

Jason

Originally Posted by 6StringJazzer

Add this code to the Sheet module for Sheet1 (Group Leader Summary). I have tested this.

Please Login or Register  to view this content.

Thanks Jeff,

I pasted this into a module... but how do i run this? I don't see it in the list of macro's to run.

Originally Posted by 6StringJazzer

You can't just paste it into a module, you must paste it into the module for the sheet containing this data.

It will run automatically any time a cell on that sheet is changed.

Thanks 6StringJazzer,

I pasted it into the sheet and seemed to work but it doesn't as I only want the (bracketed) text to turn red. Also there were some anomalies...

Example: "HWWL (PMSS) SAW" would remain all black, but I want the "PMSS" to turn red.
Example: "(PMSS) SC-HW" would turn all red, but I want the "SC-HW" to remain black.

Thanks,

Hi Jason
The worksheet change code below will do what you want for the range in Column M on your example. It will change the text colour to red from the first "(" in a cell to the last ")" in a cell. I don't know if you want to restrict it to a range or do the whole sheet. Copy & paste it into the sheet module to test.

Please Login or Register  to view this content.

Here is some code that will loop through column M & colour the cell text as desired. Put it in a normal module & call the macro from a button.

Please Login or Register  to view this content.

I know that you have the answers already, but just as an addiction, you could also use CONDITIONAL FORMATTING for this. Works fast and easy to do.
If you are interested let me know

Originally Posted by amartinez988

I know that you have the answers already, but just as an addiction, you could also use CONDITIONAL FORMATTING for this. Works fast and easy to do.
If you are interested let me know

Hi amartinez,

Yes, I know. But the challenge is that I only want part of the text to be red not the whole cell. Condition Format effects the whole cell.

Got you!

This will find the last ")" & colour all the text in between the first "(" & the last ")" red

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Shorter versions below.
I've been thinking of doing something like this to one of my journal books & until now, have been to lazy lol. Killed 2 birds with 1 stone

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Nice code ProtonLeah.
I thought about doing what you've done but I didn't know how to do it. I've worked out how your code works & now I do.
Thanks
Phil

Thanks All!

I tried all of these and they all seem to work. Now i just need to decide which one...

Thanks again!

Originally Posted by protonLeah

Please Login or Register  to view this content.

Not sure why.. but recieved a debug on

ReDim LeftRightPos(1 To StrLen, 1 To 2)

Perhaps easiest to understand.

Please Login or Register  to view this content.