+ Reply to Thread
Results 1 to 11 of 11

Conditional Formatting and list of Criteria

  1. #1
    Registered User
    Join Date
    01-21-2004
    Location
    Alabama
    Posts
    35

    Conditional Formatting and list of Criteria

    I have a column of information. The first cell has the record number. Then there are criteria the record must meet. If any of the cells have N for nonCompliant, i would like for the record number to change colors. If it were only one citeria, i think i know how to do that, but because there are as many as 10 I'm not sure how to make it change colors for all ten unless i do it one cell at a time.

    Also there are several thousand columns on different worksheets, how do i apply the conditions to each column without doing it manually?

    Thanks!

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Maybe upload a file with a sample of the data and the 10 criteria would help

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Registered User
    Join Date
    01-21-2004
    Location
    Alabama
    Posts
    35
    I tried uploading a the file. It uploaded but the only button i found was close window. After that i don't know what happened to the file.

    When the user enters N within the cells beneath the Record Number, i would like for the Record background and text to change colors. Right now i have a condition on the Y/N cells that changes the N's to bold red but I was just playing around. That is not what i want.

    I deleted all tabs but the first one--P1. There are 40 P tabs. This made the file a lot smaller so it would upload quicker.

    Thanks!

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    If you have record numbers in cells A1:Z1 and you want each of these to change colour if any of the 10 cells directly below contain "N"s then try this

    select A1:Z1

    Format > conditional formatting > formula is

    =COUNTIF(A2:A11,"N")

    choose desired formatting, OK

  5. #5
    Registered User
    Join Date
    01-21-2004
    Location
    Alabama
    Posts
    35
    Ok the file had exceeded its limit. So i had to reduce the file yet more. There were about 960 pages of the information that you see in the workbook.

    Thanks!
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-21-2004
    Location
    Alabama
    Posts
    35
    Thanks DaddyLongLegs

    But will that also apply to B2:B11 or Z2:Z11?

    Will the records for the B column the C column the Z column be affected the same?

    Thanks.

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Yes, Maurice, that's the idea, isn't that what you want?

    The clever thing to do with conditionla formatting is to select the whole range to which you want it to apply FIRST and then the formula will adjust automatically for the other columns, try it and see........

    Note: when you select A1:Z1 the "active cell" should be A1 (i.e. when you select that range, select A1 first)

  8. #8
    Registered User
    Join Date
    01-21-2004
    Location
    Alabama
    Posts
    35
    Thanks. I'm going to give it a try.

  9. #9
    Registered User
    Join Date
    01-21-2004
    Location
    Alabama
    Posts
    35
    Sorry, I didn't work. When i tried to follow your instructions, the whole range of Record Numbers was changed when the first column was TRUE.

    I've attached the file. I only want that column "heading" the record number for that column alone to highlight.

    I tried doing the single column and using the copy handle to copy the condition onto the adjacent cells but that didnt work either.

    Thanks. Please help.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-21-2004
    Location
    Alabama
    Posts
    35
    Is the only way this can be done is one-by-one?

  11. #11
    Registered User
    Join Date
    01-21-2004
    Location
    Alabama
    Posts
    35
    It has to do with relative cell reference. I typed this into the Search on MS Excel and clicked on "Switch between relative, absolute, and mixed references"

    The Dollar Sign defines what is relative and what is absolute. So if i want the column to be Relative, then i remove the $ from in front of the reference. If i want the row to be relative or to change, then i remove the $ from in front of the row number.

    So my formular came out to be =COUNTIF(B$7:B$24,"N") without the dollar sign in front of the B column. Instead of $B$7:$B$24 it is B$7:B$24. If i wanted the Column to stay absolute and the row to be relative i would say
    $B7:$B24. if i want both to be relative i would say B7:B24.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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