Conditional formatting help please - See last post. new issue. thank you.

A1 = x
B1 = y

select where is your A1/B1 (x/y)
in CF:
1st rule: =A1<B1 (red)
2nd rule: =A1=B1 (green)

Hi Sandy,

I need a bit more information. I can get the CF to work in the sheet that has the original information by using the formulas 2 parts, i.e. SUM(C15:E15)&"/"&COUNT(C15:E15) and changing the &"/"& to < or =. However this doesn't work on the sheet with the linked cell. The CF also does not translate across Is there a way of getting this to happen? Apply CF rules to a linked cell?

Its a doozy, hence why i am here.

Thanks for the first step though.

Attach a sample workbook. (Not a picture!)
Make sure there is just enough data to demonstrate your need. Include a BEFORE (original) sheet and an AFTER (required output) sheet in the workbook if needed to show the process you're trying to complete or automate.
Make sure your desired results are shown, mock them up manually if necessary. Remember, it should reflect original structure of your data.
Remember to desensitize the data.
Note:

• Please do not attach password protected workbooks/worksheets
• Please do not attach file(s) from exterior servers
• Please do not attach file(s) with enabled any Workbook Open/Autorun macros!

20 rows of data is enough (probably)

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
If link is not from FORUM server you have to wait until someone will want to watch the file from an external server.

Thanks for the advice. I will upload/attach a very brief copy as described. I have called it class and teacher, just for this example. I have put the red and green colour onto the cells in 1 example on the linked page.

I have also include small comments for these 2 cells to hopefully explain things better.

Again thank you for looking and trying to help.

One question: only 0/0 = no color?

Hi Sandy,

Yes only 0/0. The members of the sheet are being instructed to enter data on the date of capture, whether that be 1 for return or 0 for no return.

Hope that answers your question.

Thank you Sandy that is awesome.

Only issue i have come across is when 1 of the numbers is "0" it stays white. ie 0/1 stays white not red. I fixed this by removing the --left = 0 part of the line of info. Can you see that causing any issues. I cant.

Again thank you for such a quick solution. You are a legend.

Not sure if i should ask another question in a new thread or just ask here. So i will try here first.

Can you embed a formula/function into cells that stays there even if a cell has data entered into it?

For example. I have set up a cell to check the date, and reproduce an "overdue" statement if Today's date is past (referencing a due date cell). If someone then comes along and enters that this piece has been submitted by accident, it will delete the cells formulas. Is there a way that someone can enter a set response (i have defined this using data validation) but then delete this text and have the cell return to its previous formula/function?

Thank you Sandy.
The Data validation works fine, it just has a drop down box with allowable responses for those boxes. I have included the automatically generate responses as options so staff can manual re-enter mistakes. Was just wondering if there was a way. I will try the VBA path.

Again you have been more than helpful, you are a real asset to this forum.

Glad to help and thanks for the feedback

Hey Sandy,
Small issue has arise. Wondering if you or someone else can help please.
Uploaded a mock ss, hope this helps?
0/0 to stay white
0/ any number greater than 0 = red. e.g. 0/1, 0/20. etc
any number greater than 0 but less than the other part of the fraction / any number greater than 0 = orange, e.g. 1/2, 1/10, 5/15.
Both sides of the / sign =, turn green. 1/1, 20/20.

The only other way i can think of making the SS do what i want it to do is for the cell to search another sheets cells for the word refer. IF any cell has refer = red. IF no cell has refer, but does have overdue turn orange. IF no cell has refer or overdue, but has "1" for submitted, turn green. This was seems very complicated and well above my head.

Sandy so i can learn please. When you put --LEFT etc that obviously tests the left side of the "/" but why use C5,1 or U18,1? Changing these numbers or letters changes what certain cells do in my doc. Not sure why. Thanks again for your help.

Have managed to get most things working, only issue is with green working when there is more than 1 place value in either side of the "/".

Green - =AND(--LEFT(B5,1)<>0,--LEFT(B5,1)=--RIGHT(B5,1))
Orange - =AND(--LEFT(B5,1)<>--RIGHT(B5,1),--LEFT(B5,1)<--RIGHT(B5,11)) * notice the use of "11" instead of "1" needed for double digit cells.
Red - =AND(--LEFT(B5,1)=0,--RIGHT(B5,1)<>0)

Hi Bebo, thanks for the help. It is well above me what you have done, but it still has the fundamental issue. I can get it to work for either single digit fractions OR double digit fractions, but not both.

Your formatting now works for double digit fractions, but all single digit fractions like 5/5 stay white. I cant work it out either

Thank you, that did the trick. Can you please explain something to me. What part of the code allows for a choice between 1 and 2 digit numbers?

Thank you both for your input. between the both of you i have managed to get a working document up and running.

Few more things to iron out, but the conditional formatting seems to be working again.

This forum is awesome, thanks to people like you guys.

You are welcome

Mark thread solved if problem is resolved.