Macros or Find

Copper Contributor

I am seeking to find a way to search a worksheet and find cells in a column which contain a single word or two amongst the many that may be in the cell and have that trigger  a color reformat of an adjacent cell.   It could be that I just don't know the proper operating terms.  I have tried the texts "contains", "includes", and with just the words but I still cannot get it to do what I want. I believe this is possible either with a macro or in the Find function.  Any suggestions or help would be appreciated.  Thanks,  John

19 Replies

@jhicks5charternet 

You can use Conditional Formatting for this purposes.

Let's say you want to look at column D. If a cell contains the word Excel (with possibly other words), the adjacent cell in column E should be highlighted in green.

Select column E (the column you want to format).

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

 

=ISNUMBER(FIND("Excel",D1))

 

(We use D1 because it is the first cell in the selection)

Click Format...

Activate the Fill tab.

Select green.

Click OK, then click OK again.

 

If you want to highlight cells in column E if the cell in column D contains both Word and Excel, do the same, but with the formula

 

=AND(ISNUMBER(FIND("Word",D1)),ISNUMBER(FIND("Excel",D1)))

@jhicks5charternet 

You may use conditional formatting rule with formula like

image.png

Of course better not to hardcode word withi the formula, above is only to illustrate an idea.

@Hans Vogelaar Thank you so much for responding however I am have difficulty in following your directions which may be because I am using a Mac.  When I get to the Conditional Formatting screen it does not show me a place to enter your formula. I can see where it says formula under the minimum and maximum value.  That did not work for me.  This is a new subscription for me and I am transitioning from an old version.  It is almost like starting over.

Thanks. John

@jhicks5charternet 

This should work the same on Mac as on Windows.

Make sure that you select Conditional Formatting > New Rule..., and then 'Use a formula to determine which cells to format'.

S0346.png

 

S0347.png

@jhicks5charternet The screens on a Mac are a bit different. 

1) New Rule... on the Home ribbon.

Screenshot 2021-04-26 at 11.12.14.png

Then choose Classic in the Style field.

Screenshot 2021-04-26 at 11.12.33.png

Then select "Use a formula .........." from the field below the Style.

Screenshot 2021-04-26 at 11.12.57.png

Now you can enter your formula.

Screenshot 2021-04-26 at 11.13.23.png

I did enter my formula and it worked for one cell only. How do I get it to search the entire column and reformat the appropriate cells? Thanks John

@jhicks5charternet Sorry, but my response was merely to point you into finding where to enter a classic CF formula on a Mac. You haven't given us anything to work with when it comes to your requirement. If the other responses that you received don't make sense, please upload a file (without confidential information) and explain what is is you want to format and with what logic.

@jhicks5charternet 

Make sure that you select the entire column before creating the rule.

I am trying to have an executable macro which will search Column A for the word "Jamestown" and if it finds "Jamestown" it will trigger a reformat of the row to have a background color such as yellow or green. /Users/john/Desktop/Macro test sheet..xlsx/Users/john/Desktop/Macro test sheet..xlsx
My actual sheet has several thousand rows

@Hans Vogelaar Is there a way to have this formula apply to more than one column without duplicating the formula and editing. I tried ending with ,D1:C1 but that did. not work all of the time.  Thanks,  John

@jhicks5charternet 

Please explain in more detail what you want to accomplish.

I was referring to the =ISNUMBER(FIND("WORD",B1)) Formula that you helped me with recently. In stead of searching just the B Column, I would like too search B and C columns for the term "Word". I have done it by creating two formulas and I was wondering if it could be done in one. John

@jhicks5charternet 

Like this:

 

=COUNTIF($B1:$C1,"*WORD*")>0

Now I am confused. My sheet consists of 3 columns A,B, and C. My Goal is to Find 5 Different terms in either B Column or C Column. Depending upon which Term is found in either B or C or Both, A is then conditioally formatted the appropriate color. To accomplish this I have 12 Conditional Formatting formulas. I am enclosing a screen shot to Show you these. I keep thinking that I should be able to search both Column B and C and Reformat A with one formula. I haven't been able to accomplish that. I also ran into a problem when a row in A or B or both contained more than one of the terms in my search group such as "Jamestown" and "New York". Formula order took care of most of that.
/var/folders/cs/8bsmwb2x68j2nz15zj4xcq0m0000gq/T/com.apple.mail/com.apple.mail.drag/Screen Shot 2021-05-05 at 10.49.36 AM.png

@jhicks5charternet 

The screenshot hasn't come through.  Perhaps you could attach a sample workbook?

/Users/john/Ancestry/Tree people list Ancestry.xlsx
From John Hicks

@jhicks5charternet 

I'm sorry - this won't work. You have posted the path to the file on your local hard disk, but we cannot get at that of course.

Attach the workbook by dragging and dropping it into a reply.

I sent an email back with the file but I could not get it to work with your web site Reply. So I sent it regular E mail to the address that showed up when I hit respond on your incoming email. John

@jhicks5charternet 

If you wish, you can send the workbook to hans dot vogelaar at gmail dot com