0

I apologize if this has been answered before but I have had no luck in finding a viable solution to my quandary.

I am attempting to fix/enhance one of the tools we use by coloring different ranges different colors but only the rows that are used by the data being populated. I have been able to find a solution that works but for some reason it is coloring many more rows than I want it to. If I'm populating only a few rows it colors over 80 rows. The more rows I use the more rows it colors and I haven't had any luck finding out why.

The tool populates columns A-AS and starts on row 9. It is populated with data from a different tab. My tests have been unsuccessful to say the least. Here is what the tool looks like:

Quote Builder:

Quote Builder

Here is a snippet of the code I'm working with:

    ' Color used range of cells
    Sheet1.Range("A9:G9" & LastRowNo).Interior.Color = RGB(255, 242, 204)
    Sheet2.Range("A9:G9" & LastRowNo).Interior.Color = RGB(255, 242, 204)

    Sheet1.Range("H9:M9" & LastRowNo).Interior.Color = RGB(252, 228, 214)
    Sheet2.Range("H9:M9" & LastRowNo).Interior.Color = RGB(252, 228, 214)

    Sheet1.Range("N9:AI9" & LastRowNo).Interior.Color = RGB(226, 239, 218)
    Sheet2.Range("N9:AI9" & LastRowNo).Interior.Color = RGB(226, 239, 218)

    Sheet1.Range("AJ9:AS9" & LastRowNo).Interior.ColorIndex = 17
    Sheet2.Range("AJ9:AS9" & LastRowNo).Interior.ColorIndex = 17

Please let me know if you need more information, I tried to be as specific as I could.

To clarify to all that are trying to help with this issue. The code above works except that it is coloring more rows than I need it to by a significant amount. I only need it to color the used rows starting at row 9 going down to how ever many lines the user populates. This could be as few as 1 line to well over 200 lines. Basically, however many lines are needed by the user. Thanks to all in advance.

Here are the changes I've made that is a lot closer to what I need but still not all the way there:

    ' Color used range of cells
    Sheet1.Range("A9:G1" & LastRowNo).Interior.Color = RGB(255, 242, 204)
    Sheet2.Range("A9:G1" & LastRowNo).Interior.Color = RGB(255, 242, 204)

    Sheet1.Range("H9:M1" & LastRowNo).Interior.Color = RGB(252, 228, 214)
    Sheet2.Range("H9:M1" & LastRowNo).Interior.Color = RGB(252, 228, 214)

    Sheet1.Range("N9:AI1" & LastRowNo).Interior.Color = RGB(226, 239, 218)
    Sheet2.Range("N9:AI1" & LastRowNo).Interior.Color = RGB(226, 239, 218)

    Sheet1.Range("AJ9:AS1" & LastRowNo).Interior.ColorIndex = 17
    Sheet2.Range("AJ9:AS1" & LastRowNo).Interior.ColorIndex = 17
curious
  • 1,504
  • 5
  • 18
  • 32
CodeNewbie45
  • 1
  • 2
  • 1
    Remove the second `9` from all the ranges: `Sheet1.Range("A9:G" & LastRowNo).` – Scott Craner Aug 11 '17 at 16:31
  • Scott Craner - I had to make some adjustments to get this to fully work but it did the trick. Thanks for the input. – CodeNewbie45 Aug 11 '17 at 17:20
  • Scott Craner - After some further testing I found that it will only color row 9 and none of the other used rows. – CodeNewbie45 Aug 11 '17 at 17:31
  • What is the value of LastRowNo, and how is it filled? – Scott Craner Aug 11 '17 at 17:37
  • It looks at the amount of rows in the tab it's populating from so it depends on the number of rows that are filled in. LastRowNo = .Range("A1").CurrentRegion.Rows.Count – CodeNewbie45 Aug 11 '17 at 17:43
  • Debug your code and check what is the value of `LastRowNo`. You can put a breakpoint on your code after calculating value of this variable and put `?LastRowNo` in Immediate Window. – Egan Wolf Aug 11 '17 at 17:52
  • Egan Wolf - Due to the data I'm currently testing this with the value of LastRowNo is 5 but that's not a consistent value as it really depends on the amount of data the user populates in the tab it's finding that value to be. It could be as low as 2 or well over 200. – CodeNewbie45 Aug 11 '17 at 18:02
  • See here for finding the last row: https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba – Scott Craner Aug 11 '17 at 19:05
  • Scott Craner - Although that information is useful it doesn't help answer my question as I still get the same results. With your first suggestion it will only color in the first row of data and none of the following rows. With the new information concerning finding the last row I get the same result. Finding the last row isn't the problem. Why it's not coloring the rows I want is the issue. I do appreciate the input and am trying these suggestions in my testing though. – CodeNewbie45 Aug 11 '17 at 19:32
  • You said you "had to make some adjustments to get this to fully work". What kind of adjustments? Edit your question with new code. – Egan Wolf Aug 11 '17 at 20:57
  • Egan Wolf - I also said in the next comment that after further testing it wasn't working properly. I had found that my fix would only work with the data set I was currently working with and would not work with more or less rows of data. This is why I have stated that the problem isn't with LastRowNo. – CodeNewbie45 Aug 11 '17 at 21:13
  • But did you change anything in your code? If so, change it in question. – Egan Wolf Aug 11 '17 at 21:17
  • Egan Wolf - I actually changed it back to the original code above as so far it has been the best solution. I did add some clarification to the bottom of my question. – CodeNewbie45 Aug 11 '17 at 21:26
  • Ok. from this point we already know that the problem is second `9`. If `LastRowNo` is 5, then you color rows 9 to 95. – Egan Wolf Aug 11 '17 at 21:42
  • Egan Wolf and Scott Craner - I have been playing around with the code using your comments as reference. I replaced the second 9 with a 1 and am a lot closer than I was before. Now it seems to only add 1 additional colored row per populated row. I wish there was a way to get it to do what I think it should be able to do. – CodeNewbie45 Aug 15 '17 at 15:11
  • @EganWolf and @ScottCraner - I now understand why you were steering me towards `LastRowNo` as that was where I was having my problem. Thank you both for getting that stuck in my head. – CodeNewbie45 Aug 16 '17 at 19:56

1 Answers1

0

Okay, I was able to come up with a solution that works for my situation. I had to add an If statement as it wouldn't color the first row if there was only one row of data. Now it works exactly as I wanted it to.

Here is the code I used:

' Color used range of cells
If LastRowNo = 2 Then
    Sheet1.Range("A9:G9").Interior.Color = RGB(255, 242, 204)
    Sheet2.Range("A9:G9").Interior.Color = RGB(255, 242, 204)

    Sheet1.Range("H9:M9").Interior.Color = RGB(252, 228, 214)
    Sheet2.Range("H9:M9").Interior.Color = RGB(252, 228, 214)

    Sheet1.Range("N9:AI9").Interior.Color = RGB(226, 239, 218)
    Sheet2.Range("N9:AI9").Interior.Color = RGB(226, 239, 218)

    Sheet1.Range("AJ9:AS9").Interior.ColorIndex = 17
    Sheet2.Range("AJ9:AS9").Interior.ColorIndex = 17
Else
    Sheet1.Range("A9:G" & LastRowNo + 7).Interior.Color = RGB(255, 242, 204)
    Sheet2.Range("A9:G" & LastRowNo + 7).Interior.Color = RGB(255, 242, 204)

    Sheet1.Range("H9:M" & LastRowNo + 7).Interior.Color = RGB(252, 228, 214)
    Sheet2.Range("H9:M" & LastRowNo + 7).Interior.Color = RGB(252, 228, 214)

    Sheet1.Range("N9:AI" & LastRowNo + 7).Interior.Color = RGB(226, 239, 218)
    Sheet2.Range("N9:AI" & LastRowNo + 7).Interior.Color = RGB(226, 239, 218)

    Sheet1.Range("AJ9:AS" & LastRowNo + 7).Interior.ColorIndex = 17
    Sheet2.Range("AJ9:AS" & LastRowNo + 7).Interior.ColorIndex = 17
End If

If this helps anyone else then it was worth the effort. If there are other solutions or a better way of doing this then please add your information. I come to this site often to find solutions to issues and find it to be one of the most beneficial sites around. Thanks to those that offered their input in this, it was helpful.

CodeNewbie45
  • 1
  • 2