Conditional formatting on cells that contain a line feed

[photojon]

Greetings,

I have a simple single worksheet that contains all text (no formulas, VBA, etc). I am trying to create a conditional format for cells that contain "Flex Wk (linefeed) 1" and am having issues. Here are the experiments I have tried. I am able to create conditional formatting for all the cells that contain the number "1". I am also able to conditional format cells that contain "Flex Wk". I have also tried just doing a simple "Find" which works on "Flex Wk" but not "Flex Wk 1". Visually I observe the linefeed between the "Wk" and the "1". My internet searches reference CHAR(10), CHAR(13), ctrl+j, ALT 0010, etc. I have tried inserting these between the "Wk" and "1" and not of the searches find the cells. I am using Excel 2016.

Thanks
Jon

 

[photojon]

Thanks. I think we are making progress. I changed my "A1" to match the input you have above and then copy and pasted the formula into B1. It says TRUE. I then changed the "A1" in the formula to my sample cell in the spreadsheet that I manually typed and it again said TRUE. Finally, I found a cell in the "real data" of the sheet" and replaced the B1 formula to reflect that cell and once again it said TRUE. Finally, I set it to a cell I didn't think would match and it said FALSE, so we have the formula correct. I guess, now I need to figure out why the Conditional Formatting isn't working. The formatting is suppose to be changing the color of the cell. I have a few other conditional formatting statements that color on cell value and they are working fine. The conditional formatting we are working on is the first one in the list. The "Stop if True" boxes on the far right are all UNchecked. The rule says "Formula:="Flex Wk"&CHAR(10)&"1". The other conditional formatting rules that are working all either say "Cell Value =" or "Cell Value contains". Thanks again.

 

[jtakw]

The rule says "Formula:="Flex Wk"&CHAR(10)&"1". The other conditional formatting rules that are working all either say "Cell Value =" or "Cell Value contains".

Hi,

You are missing the Cell Reference as pointed out in Post # 2.

 

[photojon]

In the "Applies To" column I have "=$A$1:$AV$55" as we discussed in post #2 . I am looking at the manage rules under conditional formatting. What am I missing?

 

[MARK858]

You have

="Flex Wk"&CHAR(10)&"1"

you are missing the bit in red

=A1="Flex Wk"&CHAR(10)&"1"

 

[jtakw]

Select A1, now Left click, drag and select to AV55, Conditional Format, use Formula, enter EXACTLY this:

=A1="Flex Wk"&CHAR(10)&1

Format as you want.

 

[photojon]

Thanks! That worked great. What does the =A1 part of the equation do? I understand the rest. Really appreciate your help.

 

[jtakw]

You told Excel to look for "Flex Wk"&CHAR(10)&1 , but you didn't tell Excel Where to look.

In this case A1, and since we're using Relative Referencing ( Not Absolute ), when you select A1:AV55, the CF rule will be applied to Each and Every individual cell that is "Flex Wk"&CHAR(10)&1

 

[photojon]

Thanks! Still a bit confused. When I am looking a the conditional Formatting Rules Manager, I thought the "Applies to" column is the "range" where I wanted to have excel look. In my other conditional formatting rules, I simply say "Cell Value = 1" in the formula area, and specify the range in Applies to and it finds all the cells that have a value of "1". I hate to drag on the conversation further and really appreciate everyone's help in getting me this far. Would appreciate a bit further explanation or perhaps even a reference where I could read up on the part I am missing here. Thanks again to all!

 

[MARK858]

Maybe the below link will clarify a bit...
https://www.excel-university.com/excel-conditional-formatting-based-on-another-cell/

I thought the "Applies to" column is the "range" where I wanted to have excel look

No it is not where the conditional format looks, it is as it says where the conditional format "Applies to".

 

[jtakw]

No, Where the formula "looks" for the criteria (in this case, "Flex Wk"&CHAR(10)&1), and where this CF rule "applies to" are Two Different things.

Let me put it to you this way:

Say I have Sheet 1, A1:A10 that I want highlighted, IF, on Sheet 2, C99, is the word "Apple"

Then my CF formula rule would be =Sheet2!C99="Apple"

The applies to would be Sheet1!A1:A10 (without the Sheet1! part) since we're already on Sheet1.

So now, If Sheet2!C99 is "Apple", Sheet1!A1:A10 will be highlighted, if Sheet2!C99 is Not "Apple", then Sheet1!A1:A10 will Not be highlighted.

Hope this clarifies a bit...