conditional formatting

[iammetx]

I just recently learned about conditional formatting and I am finding it very useful. However, I just recently came across a problem that hopefully is quite simple to fix. I want a conditional formatting that looks something like this: If(cell1=A) then the cell color in cell2 should highlight blue, but, If(cell1 is blank) then the cell color in cell2 should highlight to red if they input a value.

To better understand, what I'm doing is sort of like a time sheet. If there is no time input in cell1 then they can't clock out in cell2, so cell2 should turn red if they input a time when cell1 is blank.

A means absent. The condition actually is for any word that starts with the letter A, but if cell1 has A or Absent, then cell2 should also turn blue.

Anyways, I just figured out you can't use = like that, but I don't know how else to figure out the formula If(cell1=
A) in conditional formatting.

I hope I explained myself well, and any help is greatly appreciated. Thank you.

 

[mancemonster]

Hi,

Welcome to the boards.

Highlight B2 and go to conditional format.

Change the drop down from "Cell Value Is" to "Formula Value Is"

=IF($A$1="A",TRUE,FALSE) With the format Blue
then click Add

"Formula Value Is"

=IF($A$1="",TRUE,FALSE) With the format set to Red


Hope this helps.

Regards

Roger

 

[Peter_SSs]

Roger

With Conditinal Formatting there is no need for the =IF(condition,TRUE,FALSE) structure. Just make the statement =condition
If the condition is TRUE then the CF will be applied, otherwise it won't. So your first condition would be just as good with:
=$A$1="A"

My take on the original question is this. I have selected B1:B5 and then applied the CF shown.

Excel Workbook
	A	B
1	Absent
2		9:30
3	8:15	10:22
4
5	abc	2:20
6
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	1. / Formula is =LEFT(A1,1)="A"	Abc
B1	2. / Formula is =AND(A1="",B1<>"")	Abc

 

[mancemonster]

I don't know why I did that...

I do know you don't need the If statement... Honest!

I'll put it down to a Monday morning brain fart!


Thanks for picking me up on it!


Regards

Roger

 

[iammetx]

Thank you both. Both of you helped me out.

Peter, I tried it your way, but only one worked. The one that is supposed to highlight red did not work. An IF statement was needed after all.

Mancemonster, your brain does work on Monday's after all.

And for future references (in case someone else was looking for a similar solution), when you do it this way, cell2 will always be highlighted red if the page is blank. So what I did, I went a step further and instead of doing

=IF(cell1="",TRUE,FALSE)

I did
IF(cell1< cell2<cell2"",true,false)><cell2></cell2>,TRUE,FALSE)
<cell2"",true,false)> <cell2="",true,false)><cell2,true,false)>
That worked perfectly.

Thank you both, again, so much.</cell2,true,false)></cell2="",true,false)></cell2"",true,false)></cell2"",true,false)>

 

[Peter_SSs]

An IF statement was needed after all.

You may have used one, but if it has the structure
=IF(condition,TRUE,FALSE)
then it is not needed.

Instead of referring to cell1 and cell2, could you please tell us

- what actual cell (or just the first one if several) you applied the Conditional Formating to.

- what actual CF formulas you ended up using.

- what version of Excel you are using?

 

[iammetx]

Ok, here's what I have:

When blank, the form should have a number of cells for each client for each day of the week. So, for one client, day1,2,3,4,5 looks like this (like a sign in):

<table width="128" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td colspan="2" class="xl68" style="border-right: 1pt solid black; height: 15pt; width: 96pt;" width="128" height="20"> Day 1
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" align="center" height="20">A</td> <td class="xl65" style="width: 48pt;" width="64" align="center">B
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 48pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow;" width="64" height="20">
</td> <td class="xl66" style="width: 48pt; font-size: 10pt; color: white; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow;" width="64">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 48pt; font-size: 10pt; color: white; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow;" width="64" height="20">
</td> <td class="xl67" style="width: 48pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow;" width="64">
</td><td valign="top">
</td><td valign="top">
</td> </tr> </tbody></table>
^^That's with conditional formatting. Isblank(cellname) highlight to yellow

Client clocks in and out 8:00-5:00 on Day 1

<table width="128" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td colspan="2" class="xl68" style="border-right: 1pt solid black; height: 15pt; width: 96pt;" width="128" height="20"> Day 1
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" align="center" height="20">A
</td> <td class="xl65" style="width: 48pt;" width="64" align="center">B
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 48pt;" width="64" height="20">8:00</td> <td class="xl66" style="width: 48pt; font-size: 10pt; color: white; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt; width: 48pt; font-size: 10pt; color: white; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow;" width="64" height="20">
</td> <td class="xl67" style="width: 48pt;" width="64">5:00</td> </tr> </tbody></table>
^^^Still working to get those yellow cells to turn white, when time is input correctly.

If client forgets to log in, but logs out

<table width="128" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15pt;" align="center" height="20"> <td colspan="2" class="xl65" style="height: 15pt; width: 96pt;" width="128" height="20">Day 1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none; width: 48pt;" width="64" align="center" height="20">A</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64" align="center">B</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 48pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border: 1pt solid windowtext; background: none repeat scroll 0% 0% yellow;" width="64" height="20">
</td> <td class="xl68" style="width: 48pt; font-size: 10pt; color: white; font-weight: 400; text-decoration: none; font-family: Arial; border: 1pt solid windowtext; background: none repeat scroll 0% 0% yellow;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; width: 48pt; font-size: 10pt; color: white; font-weight: 400; text-decoration: none; font-family: Arial; border: 1pt solid windowtext; background: none repeat scroll 0% 0% yellow;" width="64" height="20">
</td> <td class="xl67" style="width: 48pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border: 1pt solid windowtext; background: none repeat scroll 0% 0% red;" width="64">5:00</td> </tr> </tbody></table>
^^^CF for B2 is
<table width="315" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 140pt;" width="187"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="vertical-align: top;">Red if

</td><td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">B2</td> <td class="xl65" style="border-left: medium none; width: 140pt;" width="187">=IF($G$10<$H$11,TRUE,FALSE)</td> <td class="xl66" style="border-left: medium none; width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="vertical-align: top;">Blue if

</td><td class="xl65" style="height: 15pt; border-top: medium none;" height="20">B2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=LEFT($G$10,1)="A"</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="vertical-align: top;">Yellow if

</td><td class="xl65" style="height: 15pt; border-top: medium none;" height="20">B2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=FORMAT ONLY BLANK CELLS</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> </tr> </tbody></table>
If client is absent:

<table width="128" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15pt;" align="center" height="20"> <td colspan="2" class="xl65" style="height: 15pt; width: 96pt;" width="128" height="20">Day 1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none; width: 48pt;" width="64" align="center" height="20">A</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64" align="center">B</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 48pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border: 1pt solid windowtext; background: none repeat scroll 0% 0% rgb(0, 176, 240);" width="64" align="center" height="20">Absent
</td> <td class="xl68" style="width: 48pt; font-size: 10pt; color: white; font-weight: 400; text-decoration: none; font-family: Arial; border: 1pt solid windowtext; background: none repeat scroll 0% 0% rgb(0, 176, 240);" width="64" align="center">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; width: 48pt; font-size: 10pt; color: white; font-weight: 400; text-decoration: none; font-family: Arial; border: 1pt solid windowtext; background: none repeat scroll 0% 0% rgb(0, 176, 240);" width="64" align="center" height="20">
</td> <td class="xl67" style="width: 48pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border: 1pt solid windowtext; background: none repeat scroll 0% 0% rgb(0, 176, 240);" width="64" align="center">
</td> </tr> </tbody></table>
Same CR as above, which is:
<table width="315" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 140pt;" width="187"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td valign="top">Red if

</td><td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">B2</td> <td class="xl65" style="border-left: medium none; width: 140pt;" width="187">=IF($G$10<$H$11,TRUE,FALSE)</td> <td class="xl66" style="border-left: medium none; width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">Blue if

</td><td class="xl65" style="height: 15pt; border-top: medium none;" height="20">B2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=LEFT($G$10,1)="A"</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">Yellow if

</td><td class="xl65" style="height: 15pt; border-top: medium none;" height="20">B2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">FORMAT ONLY BLANK CELLS</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> </tr> </tbody></table>


I hope I didn't make this too confusing. I was having trouble with copying and pasting the cells, as I've never done it before. Right now I'm using a trial version of Microsoft Excel Starter 2010. I haven't got used to the new version, but I'm working with it. I'm pretty sure I must have done it wrong, but I tried it your way and the cell color would not change, even though I checked the formula, edited it around a little to see what worked, and I just couldn't get it to work with the AND function. I tried spacing in between the " " and taking out the space, but to no avail.

Thank you for your help, and let me know if an IF function was needed or not. For now, it works just fine and I can't thank you both enough.

 

[Peter_SSs]

You have shown a series of 4 cells that each appear to be cells A1:B2. However, your CF formulas refer to cells G10 and H11 so I can't really work our exactly what is happening. Are your images really the 4 cells G10:H11?

Assuming for the moment that the cells are A1:B2 as shown, can you confirm that what you want is ..

a) All yellow if all blank?

b) All no formatting if time is input correctly?

c) B2 red and others yellow if time out (cell B2) but no time in (cell A1)?

d) All blue if A1 starts with "A"?

Notes:
1. I'm a bit confused about what is "correct" time. How do we determine what is "correct". For example, your second image appears to have in at 8:00 and out at 5:00. There is no AM/PM shown so I would have thought that meant 'in' (8:00) was later than 'out' (5:00) and therefore incorrect.

2. If my assumptions in a) - d) above are correct, you will need a lot of different CF formulas - basically a set of CF formula for each of the 4 individual cells for each set of 4 cells. You could simplify that a bit by having ..

1. All 4 yellow if no entries at all
2. All 4 blue if the first cell starts with "A"
3. All 4 red if entry is incorrect
4. All 4 clear if entry is correct.

If you want to show a small clearer screen shot, including CF formulas, you can use Excel jeanie - as I did in my earlier post. Link in my signature block below.

 

[iammetx]

You have shown a series of 4 cells that each appear to be cells A1:B2. However, your CF formulas refer to cells G10 and H11 so I can't really work our exactly what is happening. Are your images really the 4 cells G10:H11?

Assuming for the moment that the cells are A1:B2 as shown, can you confirm that what you want is ..

a) All yellow if all blank?

b) All no formatting if time is input correctly?

c) B2 red and others yellow if time out (cell B2) but no time in (cell A1)?

d) All blue if A1 starts with "A"?

A thru d: Yes, that is correct.

Sorry about the formulas. I was copying and pasting from my original work formulas and had meant to input the correct cell numbers...but I forgot.

Notes:
1. I'm a bit confused about what is "correct" time. How do we determine what is "correct". For example, your second image appears to have in at 8:00 and out at 5:00. There is no AM/PM shown so I would have thought that meant 'in' (8:00) was later than 'out' (5:00) and therefore incorrect.

I know, but the sheet I'm working on has these times input by someone else and I'm trying to work with the style they use. They don't input AM/PM because it is assumed that the hours are from 8am-5pm. This is causing me a lot of problems in my other formulas where I have to calculate what meals were had during the day. Maybe you can help me out with that later? ray:

2. If my assumptions in a) - d) above are correct, you will need a lot of different CF formulas - basically a set of CF formula for each of the 4 individual cells for each set of 4 cells. You could simplify that a bit by having ..

1. All 4 yellow if no entries at all
2. All 4 blue if the first cell starts with "A"
3. All 4 red if entry is incorrect
4. All 4 clear if entry is correct.

If you want to show a small clearer screen shot, including CF formulas, you can use Excel jeanie - as I did in my earlier post. Link in my signature block below.

Thank you for the suggestion. I was thinking along those lines as well, but this is the first time I've actually worked with CF formulas, so it was trial and error. I will do exactly as you suggest by simplifying.

My question now would be, is there a way to copy these CF formulas to other cells in this exact same order or do I have to manually input these into each and every cell? There's a total of 15 clients per page, each inputting time in this exact same format for a week per page. So column1 is clients name, and column2 thru column6 are the days of the week, each day represented by the 4 squares similar to the ones I show above (A1:B2).

BTW, I had tried Excel Jeanie earlier today, but I could not download it. It kept giving me this message:

Attention - Attention - Attention

Excel has to be closed!

Excel may not be opened by any user!

But I had excel closed, so I proceeded anyways and it gave me another message:

No excel!

And I could not get it to continue after that without it giving me the same message over and over, even though I had closed all my excel.

 

[Peter_SSs]

I'm still struggling to fully understand your layout (see next paragraph) and exact requirements but hopefully this will be enough to get you going anyway.

Columns 2 to 6 are days of the week and each day occupies 4 cells similar to what you had shown. Columns 2 to 6 is 5 columns and each group of cells uses 2 columns so that would only allow for 2 1/2 days by my calculation.

Anyway, for the sake of trying something I have tried to set something up in six sets of cells:
A1:B2
D1:E2
A5:B6
D5:E6
A9:B10
D9:E10

The steps I followed were ..

1. Set up the CF as shown below individually for each of the 4 cells in A1:B2. (Note that B2 has one condition different to the other 3 cells)

2. Select A1:B2

3. Double-click the Format Painter

4. Single click on A5, A9, D1, D5 and D9

5. Click the Format Painter to de-activate it.

The actual formats may still not quite be what you want but the process may help and if you need further help with the actual formats, post back with more details.

Excel Workbook
	A	B	C	D	E
1	8:00			Absent
2
3
4
5	8:00			xyz
6		5:00
7
8
9
10					5:00
11
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =AND(ISNUMBER(B2),NOT(ISNUMBER(A1)))	Abc
A1	2. / Formula is =LEFT(A1,1)="A"	Abc
A1	3. / Formula is =COUNTA(A1:B2)=0	Abc
B1	1. / Formula is =AND(ISNUMBER(B2),NOT(ISNUMBER(A1)))	Abc
B1	2. / Formula is =LEFT(A1,1)="A"	Abc
B1	3. / Formula is =COUNTA(A1:B2)=0	Abc
A2	1. / Formula is =AND(ISNUMBER(B2),NOT(ISNUMBER(A1)))	Abc
A2	2. / Formula is =LEFT(A1,1)="A"	Abc
A2	3. / Formula is =COUNTA(A1:B2)=0	Abc
B2	1. / Formula is =AND(ISNUMBER(B2),NOT(ISNUMBER(A1)))	Abc
B2	2. / Formula is =LEFT(A1,1)="A"	Abc
B2	3. / Formula is =OR(COUNTA(A1:B2)=0,AND(ISNUMBER(A1),B2=""))	Abc

Are you saying that you couldn't even download Excel jeanie from their website? Or did you mean you had troble trying to istall or use it? When you follow the link in my signature to their website, there is a link in the left hand column called "How To". If you click that link, it opens up some more choices in that left hand column that may help you.