I'm trying to find a way to let's say input the text "3b" in one cell of a specific range of cells in a specific sheet (let's say cells range: J19:O500 and sheet name:WWP), action which will automatically change that cell's background to blue color and the letter "b" to blue color as well (note that only the "b" letter changes to blue color, the goal is having the letter "b" technically "hidden" in the background color and only visually leaving the "3" text in black color). The numbers in the text will be variable (1, 2, 3, 4, 10, 20, up to 99), but the letters will only be four (b=blue, r=red, g=green, y=yellow). Currently, our teams are manually coloring hundreds of cells every week in a standard file we use, so it would be very valuable if we can make this process automatic by allowing our teams to input the "number" they want for the cell plus a "letter" that will automatically format the cell (b, r, g, y). I understand Macros is an option, but I don't have a lot of experience on that. Thank you very much for your suggestions.

  • 1
    It will require vba, as Conditional Formatting will format all the text and not just the letter and a formula in itself cannot change the format of a cell. You will need to code something in the Worksheet_Change Event. – Scott Craner Sep 18 '17 at 20:30
  • 1
    Do you really want to keep the trailing letter, or do you just want the number and the cell color? That is, would it be desirable to have a macro that took an entered value of 35b and turned it straight into a value of 35 and a cell color of blue? – Joffan Sep 19 '17 at 0:13
  • this is the link: answers.microsoft.com/en-us/msoffice/forum/… – Jorge Rincon Sep 20 '17 at 17:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.