+ Reply to Thread
Results 1 to 9 of 9

Particular highight of both rows and columns

  1. #1
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Particular highight of both rows and columns

    I am analysing a large data worksheet (Sheet1) which consists of 21 columns (A-U) and over 35000 rows.
    Data are sorted in groups of contiguous rows.
    All data groups are contiguous, ie no empty rows separating them.
    ColP reflects in its cells the number of rows of each group. So if a group is made up of three rows, ColP shows in each cell in its column, of that group, number 3 as demonstrated in Sheet1 of the attached example.

    I am in need to:
    1. highlight each group of rows alternately in two different colours as per Sheet2 of the example, and
    2. highlight, in a third colour, ColP cells for the data group which are not in the same number as shown in those cells. As per Sheet2 of the example, cells P4-P12 are highlighed in blue because the group is of 9 rows not 11. The same applied on cells P24-P39 because the group is of 16 rows not 17 as shown in ColP.

    Can I get some expert assistance with this need, please?

    Many thanks in advance.

    T.
    Attached Files Attached Files

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Particular highight of both rows and columns

    Maybe :
    Please Login or Register  to view this content.
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Particular highight of both rows and columns

    Excellent work Karedog. When I ran the code on my sent example, it did what is required, but actually highlighted cells P13-P16 which are not needed to be highlighted as there's nothing wrong with them.
    Could you please run it on my example to see what I mean.
    I'm always more than obliged for your precious assistance.
    Thank you very much indeed.
    T.

  4. #4
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Particular highight of both rows and columns

    Terry,

    You said :
    Quote Originally Posted by terryhenderson View Post
    highlight, in a third colour, ColP cells for the data group which are not in the same number as shown in those cells. As per Sheet2 of the example, cells P4-P12 are highlighed in blue because the group is of 9 rows not 11. The same applied on cells P24-P39 because the group is of 16 rows not 17 as shown in ColP.
    Now if I look at P13-P16 (they are 4 rows), the value of the cell is 2 (not 4), so by your requirement above, isn't this one also must be marked as blue ?

    CMIIW

  5. #5
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Particular highight of both rows and columns

    Your are 100% right. But actually, they are two groups of 2 rows. They are highlighted in Sheet2 in two colours.
    Now you're making me think how to differentiate between the correct groups and the mistaken ones?
    Well, in this case I say I will run your code on the real data sheet, and as I will filter by ColP highlighted cells, I will ignore what I consider correct groups.
    In other words, this problem is solved and added to your "ammunition" of genius code.
    Thank you very very much Karedog for your constant precious help. You left me speechless.

    All the best ..
    T.

  6. #6
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Particular highight of both rows and columns

    You are welcome Terry. In case there is/are consecutive same group of ColP, please try the code below, I don't know if this is actually you needed.

    Please Login or Register  to view this content.
    Regards

  7. #7
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Particular highight of both rows and columns

    Yes .. definitely yes .. you did it again Karedog .. 100% right .. nothing wrong at all ..

    Always .. Thank you my dear ..

    T.

  8. #8
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Particular highight of both rows and columns

    You are welcome, thanks for marking the thread as solved.

    Regards

  9. #9
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Particular highight of both rows and columns

    @ terryhenderson

    Karedog provided you with an excellent solution, and I hope you have awarded him some Rep Points!

    Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

+ 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. Highight a range of numbers
    By MarkM123 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-24-2016, 07:28 PM
  2. Replies: 1
    Last Post: 05-30-2015, 03:12 PM
  3. [SOLVED] Formula to transpose rows to new columns and columns to new rows in Excel
    By bjnockle in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-26-2014, 06:28 PM
  4. Highight Duplicate Phrases in a column
    By daneff in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-14-2014, 09:35 AM
  5. how to stop command button working if persifix cell is highight
    By sspreyer in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-31-2013, 09:29 AM
  6. Replies: 0
    Last Post: 11-28-2007, 06:46 AM
  7. Duplicates! Count, Remove, Highight, etc...
    By ImpetuousRacer in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-21-2007, 03:02 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