Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I want to use conditional formatting on a formula not on the result of the formula. In other words, I'm teaching my students how to write formulas and use functions. I know they can look through a column of numbers and find the highest number. I want to know if they put the formula (=MAX(range) in the cell. I know I can use the CTRL + ` to toggle the display of the formulas but the easiest thing will be for me to turn the cell green when they put the correct formula in the cell. My idea is that I will have the cells turned red where they are supposed to put a formula. It will turn green when they have done it correctly. Stated another way, if the highest number is 89, they could put 89 in the cell and that would be wrong and the cell should stay red. If they put "=MAX(range)" in the cell and the result is 89, that would be correct and the cell should turn green. I have asked several people and looked in online help and on the web to no avail. Thanks for any help on this one.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Select A1
    Insert- name -define

    Name:
    GetFormula
    Refers to:
    =GET.CELL(6,A1)
    [OK]

    Select the cell of interest and make the background red
    format - Conditional formatting
    formula is:
    =GetFormula="=MAX(A1:A10)"
    Format
    pattern(tab)
    Choose the green background
    [ok][ok]

    Adjust the formula as needed. You can include an OR if desired...

    Steve

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Steve, it took me a while to figure out where the different parts go but I finally got it. The named range must be at the top of the cells for the range in the MAX function argument. I had to go online and see what the first argument (6) was all about but I understand now. Thanks a million.

  4. #4
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    As long as the numbers I want to run a function on with my formula are in a continous range, this seems to work. However, I have results at the bottom of columns in (B3538) and again in (G35:I38). I want to compare these monthly results in (K35:M38) to determine which month has higher results. In other words I want to have the students put in K35 the formula "=G35-B35". Since there are labels and a blank column between D and G (columns E & F), when I name the range in B35 and then put the Conditional Formatting rule in K35, it does not turn my cell green. Any thoughts on that?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am afraid I don't follow the question (I am a littly "iffy" on how it even relates to the original question). Could you post an example file and explain what you want/need?

    Steve

  6. #6
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hopefully this will help clarify things. The cells that have turned green are working as expected. The ones that are still blue aren't working. I've named a range in B35 and put conditional formatting in K35 and L35.
    Attached Files Attached Files

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Change the GetChange to the refers to:
    =GET.FORMULA(Sheet1!K41)

    [Note: This uses RC referencing which makes all the formulas identical.]

    Then select K35:M38 and set the cond format to:
    =GetChange="=RC[-4]-RC[-9]"


    Steve


  8. #8
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    That works. I don't know how or why but it works. I don't get the Get.Formula thing since that cell is empty. The RC thing makes sense as a relative reference to take the cell 4 to my left and subtract the cell 9 to my left. I see that however this works, it is letting me use the same formula in all those cells in the K35:M38 range but I don't see what K41 has to do with anything. Is the Get.Formula a VBA thing? I'm not finding much about it online. Thanks again.

    Now, I suppose I should protect my sheet after I take out the correct formulas so that some bright student doesn't just go to Conditional Formatting and see what formula I have for the cells. I suppose if they know enough about Excel to do that, they probably already know how to do these simple formulas. I'm going to try this again in a week or two with more complicated functions like FV() and NPER() and nested IF(). Hopefully I'll be able to apply what I've learned here to a new spreadsheet.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I happened to have K41 selected when I copied the named refers to. Typically you would enter this this based on the selected cell at the time...

    I apologize, I should have made that more clear...

    Get.Formula and Get.Cell are old functions from macro pages in Excel (before the introduction of VB) and only remain for legacy/compatibility purposes (though they do allow these types of things...) You can download the help file (macrofun.hlp) from http://support.microsoft.com/kb/128185

    Steve

  10. #10
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I downloaded the help files. Thanks again for your help.

  11. #11
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I have my spreadsheet working almost the way I want it to work and I've applied what I've learned to another one as well. The problem I've encountered now is that if a student puts the correct formula in a cell at the bottom of a column (=sum(D332)), the cells changes colors as I wanted it to. However, if the student copies that cell and pastes it somewhere else or if the student uses the little box at the right hand corner to copy the cell to the right I find that this copies my conditional formatting as well. In another column, I may want them to use a different formula and have the conditional formatting for that cell setup to recognize that desired formula (=E34/F34). The problem is that my conditional formatting is being overwritten when the students copy right and then the cell turns green so they think they have done it correctly. I have protected my sheet but I don't see a way to keep them from being able to copy the correct formula from one cell and pasting it into another cell where it should be incorrect but it looks like it is correct because the cell turns the 'right' color for them. If you look in the attached file, I hope this will make sense.
    Attached Files Attached Files

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't think there is any simple workaround: copying cells copies the formatting and conditional formatting is a format. To prevent this would require some protection of the sheet which would seem to defeat the purpose of the user editing and adding a formula.

    Steve

  13. #13
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Another way to do this might be to have an "answer key" worksheet - that is locked and protected. Have the conditional formatting in that worksheet. The students can copy/paste all they want in the "working" worksheet and compare their results in the answer key.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  14. #14
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I'm not sure I follow you here. It seems like you are saying to take out the conditional formatting in the 'working' sheet and put it into an 'answer' sheet. I'm not sure what the students would copy and paste into the answer sheet? Are you thinking I would have instructions that say to do all their work in the working sheet and when they are finished copy all the cells with formulas and paste those into the 'answer' sheet? If they can copy and paste into the 'answer' sheet, aren't I back to the same problem expressed earlier today? If I lock the cells to prevent the conditional formatting from being able to get changed, I think I will preclude them from being able to paste into that cell. If I let them paste into a cell, it doesn't seem that I can prevent the conditional formatting rules from changing.

  15. #15
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    This is a sample of what I was thinking of.[attachment=88079:Book1.xlsm]
    I haven't protected the answer key sheet
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •