{"id":7169,"date":"2022-09-07T06:43:07","date_gmt":"2022-09-07T01:13:07","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/"},"modified":"2022-09-07T06:43:07","modified_gmt":"2022-09-07T01:13:07","slug":"solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/","title":{"rendered":"[Solved] Excel VBA &#8211; Finding the beginning and end of coloured rows"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-31565556\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"31565556\" data-parentid=\"31565028\" data-score=\"0\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>How&#8217;s this?<\/p>\n<pre><code>Sub findColoredRows()\nDim startCol As Integer, endCol As Integer, o As Integer\nDim ws            As Worksheet\nDim i As Integer, k As Integer\nDim startRow As Long, endRow As Long\nDim cellColor As String, noColor As String\nDim cel           As Range\n\nnoColor = -4142              ' this is the color index of NO coloring\n\nk = 3\n\nSet ws = ActiveSheet\nWith ws\n    startRow = .Cells(1, 3).End(xlDown).Row\n    startCol = .Cells(1, 3).Column\n    Do While startRow &gt; 100  ' I assume your table starts before row 100.  So, if there's no data before row 100, check next column\n        k = k + 1\n        startRow = .Cells(1, k).End(xlDown).Row\n        startCol = k\n    Loop\n\n    'Now, we have our starting row - get end row.\n    endRow = .Cells(startRow, k).End(xlDown).Row\n\n    endCol = .Cells(startRow, startCol).End(xlToRight).Column\n    Debug.Print \"Start row: \" &amp; startRow &amp; \", start column: \" &amp; startCol\n\n    ' How many non colored cells is there in our range?\n    Dim noColorCells As Integer\n    For Each cel In .Range(.Cells(startRow, startCol), .Cells(endRow, endCol))\n        If cel.Interior.ColorIndex = noColor Then\n            noColorCells = noColorCells + 1\n        End If\n    Next cel\n    Debug.Print \"There are \" &amp; noColorCells &amp; \" non colored cells.\"\n\n    .Cells(startRow - 1, endCol + 2).Value = \"Start Date\"\n    .Cells(startRow - 1, endCol + 3).Value = \"End Date\"\n\n    'reDim the array to fit the colored cells\n    ReDim tDates(1 To noColorCells + 1)\n    i = 1                    'index starts at 1, so set this to 1\n\n    For k = startRow To endRow\n        For o = startCol To endCol\n            If .Cells(k, o).Interior.ColorIndex = noColor And .Cells(k, endCol + 2) = \"\" Then\n                .Cells(k, endCol + 2).Value = .Cells(k, o).Value\n            ElseIf .Cells(k, o).Interior.ColorIndex = noColor And .Cells(k, endCol + 2) Then\n                i = i + i\n                .Cells(k, endCol + 3).Value = .Cells(k, o).Value\n            End If\n            ' i = i + 1\n        Next o\n\n        i = i + 1\n    Next k\n\nEnd With\n\nMsgBox (\"Done!\")\n\nEnd Sub\n<\/code><\/pre>\n<p>This sub will find the addresses of any colored cells.  If you can explain more what you mean by &#8220;locate the beginning and the end of coloured rows in a table.&#8221; I can tweak this.  Can you post an image of a sample table maybe? <\/p>\n<p>Edit: Per discussion below, try this in case there&#8217;s not always data in the table, but you want the columns of the colored cells:<\/p>\n<pre><code>Sub findColoredBGCells()\nDim startRow As Integer, endRow As Integer, i As Integer, k As Integer, startCol As Integer, endCol As Integer\nDim cellColor As String, noColor As String\nDim ws As Worksheet\n\nSet ws = ActiveSheet\nnoColor = -4142\n\nWith ws\n    'Get the starting row\n    startRow = .Cells(1, 1).End(xlDown).Row\n    endRow = .Cells(startRow, 1).End(xlDown).Row\n\n    ' Since we know where the names start and end (less ONE for the \"Names\" part), let's count how many names we have\n    Dim noNames As Integer\n    noNames = endRow - startRow\n    If Not IsEmpty(.Cells(1, 1)) Then ' Get the first used column with data\n        startCol = 1\n    ElseIf IsEmpty(.Cells(1, 1)) Then\n        startCol = .Cells(1, 1).End(xlToRight).Column\n    End If\n\n    endCol = .Cells(1, startCol).End(xlToRight).Column\n\n    'Now we have our range, let's use it to loop for blank cells, and add those to an array\n    Dim coloredCells() As Variant\n    ReDim coloredCells(1 To noNames, 2)\n    Dim rng As Range, cel As Range\n    Set rng = .Range(.Cells(startRow, startCol), .Cells(endRow, endCol))\n    'rng.Select\n\n    'Now, count how many cells are not blank background\n    Dim cnt As Integer, celRow As Integer, lastCelRow As Integer\n    i = 1\n\n    lastCelRow = 2\n    For Each cel In rng\n        cel.Select\n        celRow = cel.Row\n        If cel.Row &lt;&gt; lastCelRow Then 'This is so we can change the first dimension in the array\n            k = k + 1\n            coloredCells(k, 0) = .Cells(cel.Row, 1).Value\n            i = 1\n           ' i = i + 1\n        End If\n\n        If cel.Interior.ColorIndex &lt;&gt; noColor Then\n            cnt = cnt + 1\n            If i &gt; 2 Then i = 2 'Since it's only two dimensions we need, only go up to '1'\n           ' ReDim Preserve coloredCells(noNames, i) 'resize the array to hold the new column\n            coloredCells(k, i) = .Cells(1, cel.Column).Value\n            i = i + 1\n        End If\n        lastCelRow = celRow\n    Next cel\n\nFor k = 1 To UBound(coloredCells)\n        Debug.Print coloredCells(k, 0) &amp; \" Start Date: \" &amp; coloredCells(k, 1) &amp; \", end date: \" &amp; coloredCells(k, 2) &amp; \".\"\n        .Cells(2 + k, 2).Value = coloredCells(k, 1)\n        .Cells(2 + k, 3).Value = coloredCells(k, 2)\nNext k\n\nEnd With\n\nMsgBox (\"Done!\")\n\nEnd Sub\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">38<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved Excel VBA &#8211; Finding the beginning and end of coloured rows <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] How&#8217;s this? Sub findColoredRows() Dim startCol As Integer, endCol As Integer, o As Integer Dim ws As Worksheet Dim i As Integer, k As Integer Dim startRow As Long, endRow As Long Dim cellColor As String, noColor As String Dim cel As Range noColor = -4142 &#8216; this is the color index of NO &#8230; <a title=\"[Solved] Excel VBA &#8211; Finding the beginning and end of coloured rows\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/\" aria-label=\"More on [Solved] Excel VBA &#8211; Finding the beginning and end of coloured rows\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[400,401],"class_list":["post-7169","post","type-post","status-publish","format-standard","hentry","category-solved","tag-excel","tag-vba"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] Excel VBA - Finding the beginning and end of coloured rows - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Excel VBA - Finding the beginning and end of coloured rows - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] How&#8217;s this? Sub findColoredRows() Dim startCol As Integer, endCol As Integer, o As Integer Dim ws As Worksheet Dim i As Integer, k As Integer Dim startRow As Long, endRow As Long Dim cellColor As String, noColor As String Dim cel As Range noColor = -4142 &#039; this is the color index of NO ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-07T01:13:07+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Excel VBA &#8211; Finding the beginning and end of coloured rows\",\"datePublished\":\"2022-09-07T01:13:07+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/\"},\"wordCount\":99,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"excel\",\"vba\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/\",\"name\":\"[Solved] Excel VBA - Finding the beginning and end of coloured rows - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-09-07T01:13:07+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Excel VBA &#8211; Finding the beginning and end of coloured rows\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Excel VBA - Finding the beginning and end of coloured rows - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Excel VBA - Finding the beginning and end of coloured rows - JassWeb","og_description":"[ad_1] How&#8217;s this? Sub findColoredRows() Dim startCol As Integer, endCol As Integer, o As Integer Dim ws As Worksheet Dim i As Integer, k As Integer Dim startRow As Long, endRow As Long Dim cellColor As String, noColor As String Dim cel As Range noColor = -4142 ' this is the color index of NO ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/","og_site_name":"JassWeb","article_published_time":"2022-09-07T01:13:07+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Excel VBA &#8211; Finding the beginning and end of coloured rows","datePublished":"2022-09-07T01:13:07+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/"},"wordCount":99,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["excel","vba"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/","url":"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/","name":"[Solved] Excel VBA - Finding the beginning and end of coloured rows - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-09-07T01:13:07+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-finding-the-beginning-and-end-of-coloured-rows\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Excel VBA &#8211; Finding the beginning and end of coloured rows"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/7169","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=7169"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/7169\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=7169"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=7169"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=7169"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}