if cell A2 contains specific text - color the background of cell G4

Hello,
I am new to formulas/macros, so I am not sure if what I am asking is a formula or a macro or even possible.
I am trying to color the background of certain cells based on specific text in a column/cells.

Untitled-2.png

Column J receives responses from a form,
Columns Y, Z, AA, AB (row 6,7) contain matches for column J's input. (They start off with white backgrounds, as data gets input in Column J, the appropriate match in Columns Y, Z, AA, AB (Rows 6,7) will have their background turn blue when it matches the specific text in Column J)

Any help would be, well, helpful

PS - If I get this far, my next issue would be can I color an empty cell in background (Y, Z, AA, AB (row8) and include the specific text, this would also come from column J but the difference would be that the input from the form is a fill in called other so I would not know the string being entered. So the bg gets colored AND the string gets entered.

Thanks for your time!

rad1964

You would do this using Conditional Formatting, whereby you can set up some condition and have the cell's format change if that condition is met.

I can't advise you in more details than that, as I can't view .png files on this forum (software incompatibilities with some browsers).

It would help if you attached a sample Excel workbook. To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

Hello Pete,
I have attached a workbook with columns J and Y, Z, AA, AB.

Look forward to your response.

Thank you in advance.

Robert

Select the cells from Y6 to AB7 and click on Conditional Formatting | New Rule | Use a formula... (bottom of list), then enter this formula in the formula box:

=ISNUMBER(MATCH(Y6,$J$6:$J$20,0))

Click on the Format button | Fill tab and choose your colour (light blue), then click OK twice to exit the dialogue box. Excel will automatically adjust the Y6 to suit the cells that had been selected, and you should find three of them have been highlighted with a blue background. Note that I have used a range of J6:J20 in the formula, although you have only used up to J9 in the example, so if you add any more data to column J in that range it may cause other cells to change colour.

Hope this helps.

Pete

Hey Pete,
That works perfectly!

Now what about the random input, if column J has a string that is not represented in the Y6-AB7, can we get it to color an empty cell in Y8-AB9 and input the unmatched string in addition to coloring the bg?

Thanks!

rad1964

I'm not exactly sure what you mean. Referring to your example file, the text "FSPE Facilities Data Technologies" appears in J9, but there is no match for it in the cells Y6:AB7, so are you saying that you would like that text to appear in cell Y8 and for it to have a different background colour?

If that is the case (and I'll wait for your response before going ahead, in case I've misinterpreted what you want to do), then you will need a formula in cells Y8:AB9 which will return the offending text or a blank, PLUS you would apply conditional formatting to those cells such that the background colour only changes if the cell does not contain a blank.

Hope this helps.

Pete

Hi Biff,

are you the keystroke policeman now? (bg)

Pete

Well, if you have changed the layout of the file you will need to re-submit it, as all the cell references will have changed.

In Conditional formatting you are not allowed to use direct references to another sheet - but, you can get around this by setting up named ranges - and I suspect that this is your problem. So, if you attach your latest file I can try to fix it for you. (Pity that, because I did go ahead last night and developed something along the lines that I described in Post #6, but I was waiting for you to confirm my suppositions before posting it).

Pete

Yes, I've seen you make that comment about your typing in a few posts in the past - I would have thought that with your prolificacy, that your typing would improve over the years.

I subscribe to the Get It Right First Time mode of typing (having learned to touch-type on old-style manual typewriters in the 60's).

Pete

Attached is my sheet.
Google Sheets forum is working on why the main formula does not work in google sheets, meanwhile, how would I code the text that does not have a match, ie coloring the block like before AND copying the non-matching text to the blank spot in the table/columns.

Thanks,

rad1964