Conditional Formatting (CF) only applies to empty cells. Why?

English
#1

If I apply CF to a cell that has data in it conditional formatting doesn’t work. if I delete the cell contents CF applies to that cell. Why won’t it format cells if there’s content in them?

In testing it works if I use a range of cells that touch each other. I’m not using a range. I’m trying to highlight specific, random, cells so the content in them is highlighted with bold font and a colored background.

For example if =Today ( ) is in A1 and the date is 2/24/22 I want cell b4 that has the text ‘February’ in it, cell M4 that has the text ‘24’ in it and cell M2 that has the text ‘7’ in it to have a red background, apply the bold font effect to the text that’s in those cells, and change the font color to white.

When I set CF up it only changes the background color of the cells red when I delete the text from those cells. If I re-enter text the conditional formatting goes away again.
Why is this happening? What do I need to do so CF works right?

To see what I mean select cell B5 and press the delete key in the example file. When the cell is empty it works, when the cell has content it…doesn’t. Why is it doing this?
16daycalendar.ods (23.4 KB)

#2

Please have a look here, if it helps you:

Bedingte Formatierung - ganze Zeile farblich markieren per Dropdown Menü

See also:
Bedingung xyz rot 69576.ods (28,6 KB)
Please note the different sheets

In addition Conditional formatting guide EN

#6

I don’t understand whatever language that is so it’s impossible for me to understand any of that. It might be helpful if it were in English so I could understand it. Please answer in English.

#10

There has to be an easier way to do this than manually entering LeroyG’s formula into CF for every single cell I want highlighted on each date. There’s 14 cells I want highlighted each day of each year, regardless of the year. That’s 5,110 times I have to do this continuing to do it to each individual cell manuallly. Is there not an easier way like click the selected cells dot and drag it somehow? Maybe a macro? I don’t know how to write macros.

For some reason even if I select a range or multiple cells separated by commas in the range field of the CF options it only formats one cell. No error, no prompt, nothing. What’s going on here?

#3

Condition: Formula is, and value is: LEFT(TEXT(B3;"mm/dd/yy");3)="02/".
16daycalendar_LeroyG.ods (15.2 KB)

EDIT: Two steps closer:
Condition for cells C3:AH3: DAY($B3)=INDEX(C$4:C$15;MONTH($B3))
Condition for cells B4:B15: TEXT(B$3;"mm")=TEXT(ROW()-3;"00")
I can’t achieve to highlight the actual day in the actual month row.
16daycalendar_LeroyG_2.ods (18.9 KB)

EDIT 2: While I was solving, I was learning.
Condition for cells C4:AH:15: AND($B4=TEXT($B$3;“mmmm”);DAY($B$3)=CELL(“CONTENTS”))
16daycalendar_LeroyG_3.ods (20.7 KB)
16daycalendar_LeroyG_3
Locale setting language must match the months language, so I changed to English. It shouldn’t affect your system. In Spanish month names (B4:B15) must be in lowercase.

Done with LibreOffice 7.1.8.1.

3 Likes
#4

TEXT converts a number to text and applies the given number format. However B3 contains no number, so TEXT returns the string “February”. You could find this out by yourself if you would simply enter TEXT(B3;“mm/dd/yy”) into some empty cell. The result is “February” and the 3 leftmost characters are “Feb” which is never “02/”

#9

That works great. Thank you, LeroyG.

1 through 16 twice in row 3 indicate what products are made on those dates.
How do I get it to highlight the day, and 1 through 16 that corresponds to the date as the date changes? Do I have to enter hundreds of conditional formatting, one for each month, one for each day, and one for each product day? That’s over 1000 conditions being formatted. I was hoping it could be done with fewer CF’s.
I’m trying to get it to change as the date changes.

#11

I don’t tested yet, but you must use the Date&Time category functions; e.g. MONTH, DAY, YEAR.

EDIT (after a power outage): You will need the COLUMN and ROW functions adding some positive or negative value. Not sure that I can test it today.

#12

I’m not positive what you mean by that. The year doesn’t matter. I’m trying to design this so it works indefinitely regardless of the year. That’s why Monday through Sunday is not included and I asked for help with a way to do it without including the year.
I’m far from an expert with excel and even less knowledgeable with how this calc program works / how I have to do things in it so it does what I’m trying to get it to do.

Time of day is not considered either other than I’d like the document to change the day at 5am (05:00) every day instead of at midnight - which is a future question if I’m not able to figure that out.

Can you please example or explain how using the month and day date functions can resolve having to insert over 5000 individual conditional formatting rules?

The CF does the same thing. It needs to move forward one cell every 24 hours. There isn’t a way to do this once and drag or copy/paste it somehow?

Doing the 12 months this way wasn’t too much off a task but to do it this way to every day number and every product day number and then each data cell for the product recipe and machine settings which is another 30 cells that need the same CF rule…ouch.

There isn’t a way to apply the CF to one cell, click and drag or highlight and copy/paste?

I’m aloo confused as to why I can’t make one rule for more than a single cell. It’s not letting me set a range or even just 2 cell addresses separated by a comma.

#13

Select all the cells where you want to apply a c.f.
Any relative reference in your c.f. formula should refer to the first cell of the selected range. For instance, if you selected A1:X99 before calling the dialog and you want to format any cell in A1:X99 where the twice the value is more than the maximum of column X: A1*2>MAX($X$1:$X$99)
The relative reference A1 refers to the tested cell itself based on the first selected cell A1.

How to do the same with cell formulas:
Select range A1:A99, enter =B1*2 and hit Alt+Enter. This applies any relative reference relative to the currently active cell. In OpenOffice a relative reference in a c.f. formula also refers to the active cell instead of the first cell.

#14

Lost me with

–
Do you mean right-click with the range selected, select format cells, and write a formula like that relevant to the selected cells in the Format Code field?
…
Can you please do this with the example file I included? I’m failing to get it to apply conditional formatting to a range at all. It will only CF the first cell selected although the range field has a range in it. For example if I select A1:X22 before calling the dialog the range A1:X22 is in the range field but when I OK the dialog the only cell it CF’s is A1.
…
Why? I don’t understand why CF won’t do a range if there’s content in any of the cells in the range. Is there an option somewhere I have set incorrectly (all options are default I haven’t changed any and I have the latest version)? Is there something I have to do with the formula or an option I’m missing in the CF dialog so it’ll apply conditional formatting to cells that have content?
…
I’m a noob to calc. I don’t know all the lingo and terms and I’m far from knowing the prerequisites if there are any for using Conditional Formatting. It’s taking me many hours to apply conditional formatting to every cell that needs it. There’s thousands of them.
…
All I want it to do is if the =Today() script says it’s March 2nd “03/02/” it highlights March in column B, the 2 that’s in that row, and the cell in row 3 above the number (2) highlighted. If it’s 11/21/3099 (year doesn’t matter) highlight November in column B, the number 21 in the same row, and whatever cell is in row 3 above in the column the 21 is highlighted.
…
Sounds simple but doing it has become complicated and extremely tedious having to make a CF for every single individual cell. There’s got to be an easier way to get it to do this. Maybe calc isn’t what I should be trying to do this with? I used calc because I thought it would, without being this much work (5000+ cells to CF).

#15

If you are such a noob, then you can not use conditional formatting. Conditional formatting requires that you fully understand some basic principles. C.f. is an advanced feature.
Number vs. Text
Formula vs. Constants
Value vs. Formatting
Relative vs. absolute addressing

#17

I am here asking questions to learn. Please inform me how someone’s supposed to learn if they’re not allowed to ask questions on a subject they don’t know but are trying to learn.

#20

Thankyou so very much, LeroyG! Your second edit makes this so much easier and less time consuming than giving every cell a CF! Thank you!!!

1 Like
#16

Hi it’s hard to understand exactly what you are after as a solution … but try this as a variation of what @LeroyG already gave you.

i have added /inserted a column as a month text comparison to use in the month highlighting CF.
i also added a seperate CF to highlight the corresponding date days.

16daycalendar_LeroyG_modified.ods (14.3 KB)

i work in europeal date format (dd/mm/yy) so you may have to change back to a left formula comparison that leroy used for the US format (mm/dd/yy).

hope it helps.

#18

Thank you for your time and effort, Tonyiii!

To attempt describing what I’m trying to get this to do I’ll give several examples in MM/DD format. As I mentioned previously the year is not important. This is a production schedule for staff to read so they know what product the machines are scheduled to make on a given production day regardless of what year it is.

The production schedule is a custom 16 day schedule because there’s 16 different product types.

If the date is 03/04/22 I want the cell that says March, the cell in March’s row that’s the day number (4), and the number in row 1 above today’s date’s day number (4 in this example so it’s product day 15 in the chart):

1
11730×759 70.2 KB

If the date happens to be August 19th of any year (08/19/##) I want it to highlight August, the number 19 in August’s row, and the product day in row 1 above August 19th which is product day 7:

2
21760×752 76.8 KB

If the date happens to be May 18th of any year (05/18/##) I want the solution to be that it highlights May, the number 18 in May’s row, and the cell in row 1 above the number 18 in May’s row indicating that May 18th is product day 10:

3
31758×758 76.7 KB

I hope this helps explain what I’m trying to do. I really hope there’s an easier, quicker way to get it to do this that isn’t going to take up weeks of my time individually giving every single cell it’s own conditional formatting.

An issue I’m having I can’t figure out why the heck it’s doing it is it won’t let me make a CF rule for (example) row 24 cells A:G. In order to get the CF to apply to each cell I have to give cell A24 the CF, then B24, then C24, then D24 and so on. Why won’t it apply the CF to a range?

#19

hi Bort, mayby i just don’t get it (or what your trying to do) cos when i try to make a ranged CF it workes fisrt time no problem.

Seeattached a doc of the sample i use… to explain what i mean…
220305 Tonyi Ranged CF cell look up & highlight.ods (9.3 KB)

The formula reference “C2” is not exclusive and fixed therefore will follow relative to the appointed range.
The look up cell “$E$1” is exclusive and fixed therefore will not move relative to the appointed range.

image
image1438×476 35.7 KB

So what i think is going wrong for you is how you nest your CF formula to make the logic work.

Anyways… looks like LeoyG has posted a solution for you above _2, but may still have to ammend the CF formula to the full range and nesting logic you want to acheive what you want.

ok good luck,
Regards
Tony iii

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.