Count cells in row based on cell value, highlight row if wrong number or cells.

**gutterball** · 09-08-2014, 11:41 AM

I want to have a macro that checks the letters in column E and counts the cells in the row, and if the number does not match a preset for those letters combinations the row gets highlighted red.
In the example attached, row 2 has BLD the column E, so that row should have a total of 7 columns filled out.
So:
CP needs 5 count
BLD needs 7 count
ED needs 6 count
TBKR needs 0 count
And so on…

I will need to be able to add additional lines of code to the macro to check the more than 150 letter combinations.
Any help would be appreciated.

SampleRESULT.xlsx Sample.xlsx

**natefarm** · 09-09-2014, 12:05 PM

Well, it wouldn't be possible to have a 0 count for TBKR, so I adjusted that to 5. This should do it though.

Please Login or Register  to view this content.

**JeteMc** · 09-09-2014, 01:26 PM

Nate beat me to it, and with a nice code too I might add, but since I had worked this up I thought I would reply anyway. It highlights the same two rows that you highlighted in the 'sample' spreadsheet, so I hope it will work on your entire set of data.

Please Login or Register  to view this content.

Christ's Peace and Best of Luck

**gutterball** · 09-09-2014, 04:24 PM

Thank you so much for coding this for me. I hope others and I can learn from your experience.

**gutterball** · 09-09-2014, 04:25 PM

Thank you for your hard work helping me with this code.

**gutterball** · 09-09-2014, 04:34 PM

Originally Posted by JeteMc

Nate beat me to it, and with a nice code too I might add, but since I had worked this up I thought I would reply anyway. It highlights the same two rows that you highlighted in the 'sample' spreadsheet, so I hope it will work on your entire set of data.

Please Login or Register  to view this content.

Christ's Peace and Best of Luck

Great work!

Can only the highlighted fields be placed onto a second work sheet? Can I add this function to this code?

**natefarm** · 09-09-2014, 04:46 PM

Please Login or Register  to view this content.

**JeteMc** · 09-10-2014, 01:15 AM

Gutterball, Thanks for the compliment, I am sorry that I do not have a ready answer for the question about placing the incomplete rows in a second work sheet, I can look into that, but it may take some time. I note that Nate has already added that functionality to his code, so perhaps that would be your better option.

**JeteMc** · 09-11-2014, 04:00 PM

I added the functionality for copying and pasting the first 20 cells from each 'incomplete' row to a separate sheet.
I have also added some comments to the code that may be useful in updating it should the need arise.

Please Login or Register  to view this content.

Hope that this will be of some help.
Christ's Peace and Best of Luck

Count cells in row based on cell value, highlight row if wrong number or cells.

LinkBack

Thread Tools

Rate This Thread

Display

Count cells in row based on cell value, highlight row if wrong number or cells.

Re: Count cells in row based on cell value, highlight row if wrong number or cells.

Re: Count cells in row based on cell value, highlight row if wrong number or cells.

Re: Count cells in row based on cell value, highlight row if wrong number or cells.

Re: Count cells in row based on cell value, highlight row if wrong number or cells.

Re: Count cells in row based on cell value, highlight row if wrong number or cells.

Re: Count cells in row based on cell value, highlight row if wrong number or cells.

Re: Count cells in row based on cell value, highlight row if wrong number or cells.

Re: Count cells in row based on cell value, highlight row if wrong number or cells.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] highlight a series of cells based on the number of days in a date range

[SOLVED] Highlight Cells based on Cell Reference on another Worksheet

highlight specific cells in row based on one cell value in same row.

Highlight 1 cell based on value from other cells

Highlight or colour a cell or cells based on cells in another range.

Bookmarks

Bookmarks

Posting Permissions