Conditional Formulas for a range of cells

[nirmalar]

Hello,

I have Names in Cell 1, and time frames from 9 AM to 5 PM in each cell ranging from B - J.

When I type leave in any cell adjacent to a name, the entire cells B - J for that person should be filled by the defined color.

I tried conditional formatting, it lets me do it only for one cell at a time. I do not want to merge all the cells, also I would enter leave any one of those cells.

Names 9AM 10AM 11 AM ... 5PM
Angel leave
Barbie
Cathy leave

I am not comfortable with macros, as I also need to put this on google doc.

Thanks.

 

[anandvarma]

TRY THIS

Excel Workbook
	A	B	C	D	E
1	Names	9AM	10AM	11AM	12AM
2	Angel leave	Leave
3	Barbie*
4	Cathy leave	leave
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =UPPER($B2)="LEAVE"	Abc

 

[nirmalar]

I would enter leave any cells, as I can enter in anywhere between - B - J for one name.

 

[AlphaFrog]

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="width:30px; " /><col style="width:72.67px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center;font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Names</td><td style="text-align:right; ">9:00 AM</td><td style="text-align:right; ">10:00 AM</td><td style="text-align:right; ">11:00 AM</td><td style="text-align:right; ">12:00 PM</td><td style="text-align:right; ">1:00 PM</td><td style="text-align:right; ">2:00 PM</td><td style="text-align:right; ">3:00 PM</td><td style="text-align:right; ">4:00 PM</td><td style="text-align:right; ">5:00 PM</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Angel</td><td style="background-color:#ffcc99; "> </td><td style="background-color:#ffcc99; "> </td><td style="background-color:#ffcc99; "> </td><td style="background-color:#ffcc99; "> </td><td style="background-color:#ffcc99; ">Leave</td><td style="background-color:#ffcc99; "> </td><td style="background-color:#ffcc99; "> </td><td style="background-color:#ffcc99; "> </td><td style="background-color:#ffcc99; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Barbie</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Cathy</td><td style="background-color:#ffcc99; "> </td><td style="background-color:#ffcc99; "> </td><td style="background-color:#ffcc99; ">leave</td><td style="background-color:#ffcc99; "> </td><td style="background-color:#ffcc99; "> </td><td style="background-color:#ffcc99; "> </td><td style="background-color:#ffcc99; "> </td><td style="background-color:#ffcc99; "> </td><td style="background-color:#ffcc99; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >B2</td><td >1. / Formula is =ISNUMBER(MATCH("Leave",$B2:$J2,0))</td><td style="background-color:#ffcc99; ">Abc</td></tr></table></td></tr></table>

This could work as well.
=COUNTIF($B2:$J2,"Leave")>0