Forum Discussion

Genaide's avatar
Genaide
Copper Contributor
Mar 10, 2022

Change different numbers in a text string a certain color

I have data in excel that is a text string, example BDR103-BDR108-BDR328-BDR605, in a range of 15675 rows in one column.

 

BDR103-BDR108-BDR328-BDR605
BDR108
BDR108
BDR108
BDR101-BDR108
BDR104-BDR108-BDR401
BDR103-BDR108
BDR104-BDR108
BDR108
BDR104-BDR604
BDR103-BDR108-BDR328-BDR605
BDR104
BDR104-BDR108-BDR300
BDR104-BDR108-BDR300
BDR108
BDR101-BDR108-BDR401-BDR603
BDR108

 

I have multiple rows with different numbers after BDR and I need to change BDR103 and BDR108 blue and BDR328 and BDR605 red.  I actually have 10 specific BDR numbers I need to turn blue if they appear in the text string and 12 BDR numbers I need to turn red if they appear in the text string.  Some of the BDR#'s will stay black.  Does anyone have any clue how to make this happen?  Thanks for your help!

Macros and VBA

2 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    Mar 10, 2022
    conditional formatting -> new rule -> custom formula:
    assuming column A and it has to be the first part then you are looking at the first 6 characters or you can look at character 4-6 (both examples are here):
    =(LEFT(A1,6)="BDR103") + (MID(A1,4,3)="108")
    and set the formatting to Blue
    if the string is anywhere in the cell then
    =IFERROR(SEARCH("BDR328",A1),0) + IFERROR(SEARCH("BDR605",A1),0)
    and set format to Red
    a couple points to know is that A1 must be the upper left corner of the range that the conditional formatting is being applied to.
    the + in the equations works like an OR (if you needed to have AND then you use *)
    • Genaide's avatar
      Genaide
      Copper Contributor
      Mar 10, 2022

      Thank you, but I don't think a formula will work for what I'm trying. I think the only way to do it will be using visual basic.

      I have a range, AR2:AR15676, and all cells are filled with random text strings. I need to search these text strings for BDR10, BDR12, BDR111, BDR112, BDR113, BDR307, BDR313, BDR325, BDR326, BDR403, BDR502, and BDR600 and highlight them red in the text string when found. I also need to search for BDR1, BDR13, BDR16, BDR100, BDR110, BDR301, BDR302, BDR303, BDR329, and BDR605 and highlight them blue in the text string when found. 

Resources