Formatting based on cell text

[kgall89]

Hello everyone, I am just getting my feet wet in the formula aspects of Excel and have hit a couple roadblocks I can't seem to figure out.

I am on a Mac, running Excel 2016.

I have a list in a column of about 130 cells, each with specific text entered. Additionally each cell is formatted with a unique color fill and text color. I have this list again on 15 separate sheets in a different order. I am looking to auto format each instance of the word to match the original formatting on sheet 1.

Screen Shot 2018-09-10 at 7.48.36 PM.pngScreen Shot 2018-09-10 at 7.49.39 PM.png

Again I am trying to have the cells in picture two auto format to the cells in picture 1 with the matching text.


I think my issue arises because they are out of order in the sheets?

Thank you for your help!

[bhenlee]

if the words in Sheet1 are conditional formatted then you can simply use Format painter to copy their formats in the other sheets.

[kgall89]

Thank you for the reply bhenlee.

Unfortunately they were not conditionally formatted on Sheet1, I typed in a hex# for each fill color and text color. This was very time consuming, I am hoping a formula will help speed up the process.

[Kaper]

As far as I know, you can't achieve this with formula.

It can be done with the macro, but whenever you add some new data into dependant sheet(s) or change anything in the master formatting table, the macro would have to be executed again.

[Kaper]

If you are interested in such solution, will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data and 3-4 sheets is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If trere are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, etc. - please show them all or at least indicate in text) The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).

3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!

To attach an Excel file you have to do the following: Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.

[kgall89]

Thank you for the offer but I will just push through and manually format.

[kgall89]

Thank you Kaper for your response. That is very unfortunate to hear but I appreciate your time.

[Kaper]

Do not give up that easily

Check such macro:

Please Login or Register  to view this content.

You can test how it works on the attached file - see all worksheets, then run the macro, and watch changes it made. If it works as expected close the file, prepare copy of your original file (never work on original with not fully tested solutions).

If you can rename sheet with colors table to Master do it, and open file from the attachment.

If not, you have to change sheet name in macro code:
Open VBA editor (In Win systems it's Ctrl+F11, shall be similar on Mac). Change the name
in 4th line of code:

Please Login or Register  to view this content.

Do not close file from attachment - just make your workbook active. Run the same macro - changes shall be done in your file.

[kgall89]

Amazing work. That worked perfectly thank you so much!