+ Reply to Thread
Results 1 to 12 of 12

Conditional Formatting

  1. #1
    Registered User
    Join Date
    01-08-2015
    Location
    Australia
    MS-Off Ver
    Mac 2011
    Posts
    75

    Conditional Formatting

    Hi,
    I'm trying to set conditional formatting in B4 and then copy it down the column but I want the cell reference to change. It seems to stay the same. I'm not sure if it is to do with the cells I'm referencing. The formula i've included is
    Please Login or Register  to view this content.
    . So i would like it to change to B6 & C6 but N3 to stay the same.The cell I'm trying to use the conditional formatting on holds an array formula so I'm not sure if that is an issue.

    I've attached the file.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Conditional Formatting

    Quote Originally Posted by Littlesimon View Post
    So i would like it to change to B6 & C6 but N3 to stay the same.
    Why do you feel that the CF rule is not being applied this way? Notice that when I change the AusVELS Scores for Fred and Bob, as well as the Expected AusVELS Level, to 8 the formatting works for Bob, meaning that the CF rule is reading the values in 1 New B6 and C6.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    01-08-2015
    Location
    Australia
    MS-Off Ver
    Mac 2011
    Posts
    75

    Re: Conditional Formatting

    Sorry I left out an important piece of information. Yes it works when you put in the expected level by shading that level yellow and it puts the students name. Although as 1 New C6 = 0 then it should also colour the name. It does this in B4 but not in B5. If I go into the conditional formatting for B5 and change it to B6 & C6 then it works but that would take ages to change every cells conditional formatting reference. Hope that makes sense.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Conditional Formatting

    There is no need to change the CF rule for each cell, Excel will change the relative cell references while leaving the absolute cell reference, as seen by looking at Continuum!J4:J706 Fred Black's name is shown in black font and Bob Brown's name is shown in red font as I would expect by looking at the 4th CF rule. All of the cells have a tan fill as I would expect looking at the last CF rule. Perhaps if you could amend your attachment to post #1 by manually inserting what you want to see on the continuum sheet in B4 and B5 it would help us figure out how to write CF rules to match your expectations.

  5. #5
    Registered User
    Join Date
    01-08-2015
    Location
    Australia
    MS-Off Ver
    Mac 2011
    Posts
    75

    Re: Conditional Formatting

    Sorry I don't follow with the Continuum!J4:J706 is this reference correct? I've attached a file with examples of what I would like. In the continuum sheet it shows the current result for Bob Brown. In Continuum(2) & Continuum(3) I have changed the conditional formatting of B5 to reference 1 New B6 & 1 New C6 . Continuum(2) shows the result of Bob being red because 1 New C6 is equal to 0. Continuum(3) shows the result as the cell being yellow and the text being red as C6 is equal to 0 and the expected level on the continuum page is equal to 8. If 1 New C6 is equal to anything other than 0 then the text should be black and the cell should be white.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Conditional Formatting

    I think that I understand now. Look at the Continuum(3) sheet and see if the formatting does what you want. BTW the helper column(J) can be hidden for aesthetic purposes.
    Let me know if you have any questions.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-08-2015
    Location
    Australia
    MS-Off Ver
    Mac 2011
    Posts
    75

    Re: Conditional Formatting

    It doesn't quite work. I've attached copy 4. I haven't altered your spreadsheet except for the scores in sheet 1. The results in the Continuum 3 are:
    Bob's name is fine because it is coloured red showing no growth and the expected level is 8 so the background should be yellow.
    Fred's name has black text because he is showing progress but he should have a yellow background because the expected level is 8.
    Mary's name is red because she made no progress but it should have a white background as 7.5 isn't the expected level.
    Sarah's name is fine because she has made progress and the expected level is not 7.5.

    Let me know what you think.
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Conditional Formatting

    Where do we find the "Expected" levels?

  9. #9
    Registered User
    Join Date
    01-08-2015
    Location
    Australia
    MS-Off Ver
    Mac 2011
    Posts
    75

    Re: Conditional Formatting

    It doesn't matter as it will be used by people that have different expected levels. It is just if they have made no progress and they are at the expected level (N3 = whatever score they got) then the background should be yellow and the font red. If they made progress and are at the expected level it should be yellow background and black font. If they made no progress but they are below or above the expected level then it should be white background and red font. If they made progress but are below or above the expected level then they should be white background with black font. It is to highlight those that have made no progress in red but we want to see the expected level.

    I hope that makes sense sorry.

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Conditional Formatting

    See if this layout works. Use the Goto Special Blank method to fill in the levels in column A. Then apply the following rules:
    1) for CF of the expected level: =$N$3=$A4 applied to $B$4:$I$706
    2) for CF of student on expected level and showing progress: =AND($J4<>0,$N$3=$A4) applied to $B$4:$B$706
    3) for CF of student on expected level and not showing progress: =AND($J4=0,$N$3=$A4) applied to $B$4:$B$706
    4) for CF of student not on expected level and showing progress: =AND($J4<>0,$N$3<>$A4) applied to $B$4:$B$706
    5) for CF of student not on expected level and not showing progress: =AND($J4=0,$N$3<>$A4) applied to $B$4:$B$706
    Rule one will cover all "Blocks" of ausVELS
    Rules two through five would need to be adjusted for each column and a new helper column (i.e.K,L,M,N and O) would need to be added.
    Let me know if you have any questions.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    01-08-2015
    Location
    Australia
    MS-Off Ver
    Mac 2011
    Posts
    75

    Re: Conditional Formatting

    Thanks. I Tweaked it a bit and it works great. For no 1) I only assigned this to columns A,H,I. The others will turn yellow if they need to with 2 & 3. B:G I added a $ sign and referenced the cell in A that holds the level. For example: Level 8=AND($J4<>0,$N$3=$A$4)instead of =AND($J4<>0,$N$3=$A4). That way I didn't need to use the Goto Special Blank method. Now I just have to do the same to the other columns and I'm finished.

    Thanks again. I couldn't have done it without you!

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Conditional Formatting

    Glad that you got a solution that works for you and thank you for the feedback. Please take a moment to select Thread Tools from the menu link above and mark this thread as SOLVED. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional Formatting Removing Previous Conditional Formatting?
    By CravingGod in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2016, 01:02 PM
  2. Replies: 6
    Last Post: 01-08-2016, 06:44 PM
  3. Formatting Cells with Date or Text Values in a Conditional Formatting Formula
    By Phil Hageman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-15-2014, 09:36 AM
  4. Opening xlsm files with conditional formatting opens with removed conditional formatting
    By Martijn.Steenbakker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2014, 05:38 AM
  5. Replies: 1
    Last Post: 09-20-2013, 06:23 PM
  6. Delete Conditional Formatting conditions but keep cell formatting - Excel 2010
    By tetreama in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-09-2012, 08:28 PM
  7. Replies: 3
    Last Post: 05-15-2012, 04:13 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1