Hello,

I'm looking to set up formatting so that when, ex: 23G-11G>.99; my format applies. The problem is...I want to do that for a whole row of cells.  Essentially, any cell in 23- any corresponding cell (a for a, b for b, etc) > .99. I've tried a couple different ways but I'm not figuring it out. Thank you for any help.

Not sure if I am understanding correctly but do you mean something like the screen capture below where any value in the row is greater than .99  Note that 0.99 is equal and not greater so it is not included.

If this is what you require then as follows.

Select the entire range to which the Conditional Format is to apply.

Select "Use a formula to determine which cells to format"

Enter the following formula.

=A23>0.99

Click the format button and apply the format.

Note the formula is applied to the first cell of the selected range and Excel looks after applying it to the remaining cells.

Hi Michaelaf,

Please try OssieMac's suggestion first, if you have any further question, please upload a sample file of your data.

Regards,

George Zhao
TechNet Community Support

I hope that this might help explain, the function is easy for a single cell, but I need to do multiple rows with the same format so I'm hoping to avoid single entries.

Ex:

So in this formula, when the weight loss is =/> than 1, the cell highlights blue. But I need this for the entire row, not just column C. When I've tried to click on the row numbers far left, the formula doesn't work.

And thank you again for everyone's help

• Edited by Michaelaf 19 hours 25 minutes ago

I hope that this might help explain, the function is easy for a single cell, but I need to do multiple rows with the same format so I'm hoping to avoid single entries.

Ex:

So in this formula, when the weight loss is =/> than 1, the cell highlights blue. But I need this for the entire row, not just column C. When I've tried to click on the row numbers far left, the formula doesn't work.

And thank you again for everyone's help

• Edited by Michaelaf Friday, September 04, 2015 12:08 PM

I hope that this might help explain, the function is easy for a single cell, but I need to do multiple rows with the same format so I'm hoping to avoid single entries.

Ex:

So in this formula, when the weight loss is =/> than 1, the cell highlights blue. But I need this for the entire row, not just column C. When I've tried to click on the row numbers far left, the formula doesn't work.

And thank you again for everyone's help

• Edited by Michaelaf Friday, September 04, 2015 12:08 PM

OK. I completely misunderstood the question. Try the following:-

1. Select the cell with the conditional formatting applied (C13).
2. Select Conditional Formatting -> Manage rules.
3. Select the rule and then click Edit button.
4. Click in the field with the formula and then press F2 (F2 places it in edit mode so you can us arrow keys to edit)
5. Edit the formula to the following (Remove the $ signs to make the formula relative instead of absolute)
6. =C13-C6<-0.99999
7. Click OK to return to previous dialog.
8. Click in the "Applies to" field and then press F2 to enter Edit mode.
9. Delete the range $C$13 but leave the = sign in place.
10. Click the icon at right hand end of the "Applies to" field (Another popup appears).
11. Select cell C13 first and then hold the Ctrl key while you select the other cells that the formula is to apply to. From your screen capture I am assuming it is probably C13:G13, C20:G20 etc. (I am not sure if there is a limit to how many separate ranges you can have to apply the conditional formatting).
12. Click the icon at the right hand end of the field to return to the previous dialog.
13. The "Refers to" field should contain something like the following but maybe more cell references:  =$C$13:$G$13,$C$20:$G$20,$C$27:$G$27,$C$34:$G$34  (each non contiguous range is separated by a comma)
14. Click Apply and then OK.
15. Note: Formula is applied to the first cell of the range to which the conditional formatting is to apply and then when the "Applies to" range is adjusted then Excel looks after applying the formula to those cells. There are occasions when absolute addressing is used in the formula like if you intended to subtract the same cell from all of the cells. eg if you wanted to take the value of C6 from all of the cells then the formula would be like the following.
16. =C13-$C$6<-0.99999
17. You can also use partial absolute like if you wanted to subtract the first row (row 6) from all of the cells then like the following. When applied to column D it would subtract D6 from D13 and then D6 from D20 etc.
18. =C13-C$6<-0.99999

Hope this helps but get back to me if you are still having a problem. If so then it might be advisable to upload a copy of the workbook to OneDrive.

Guidelines to upload a workbook on OneDrive:

1. Zip your workbooks. Do not just save to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder.)
3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
4. Go to this link.  https://onedrive.live.com
5. Use the same login Id and Password that you use for this forum.
6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
8. Right click the file on OneDrive and select Share.
9. Do NOT fill in the form; Select "Get a Link" on the left side.
10. Click the button "Create a Link"
11. Click in the box where the link is created and it will highlight.
12. Copy the link and paste into your reply on this forum.

I'm pretty sure that this is working now. So far I don't think I've had any conflicts. Thank you very much for your help :), I was pulling out my hair trying to figure where I was messing up.