Macro / formatting 1

[Robertislearning]

How do I create a Macro to go through a report similar to the one below that will highlight any row where the "Response time" exceeds 3 minutes? I don't want to use Conditional formatting as it limits me to 3 conditions.


Receive Customer Response time
10:00 INR 5.0
10:15 INR 6.5
10:30 Cust 3.0
10:45 Cust 3.5
11:00 SRV 6.0
11:15 SRV 6.5

Thanks in advance for your help!!!

Robert.

 

[dyarwood]

Try the following loop in a VBA code

i = 2
Do Until IsEmpty(Cells(i,1))

If Cells(i,3).Value > 3 Then
Rows(i).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

Loop

Will highlight any row i, yellow where the value in the third column of that row is greater than 3

Let me know if that helps

dyarwood

 

[Robertislearning]

Hello dyarwood,

Thanks for your assistance. I placed the code above in a MVB window but when I try to run the macro I get an error that reads: "Compile error: Loop without Do"

Can you see where I'm going wrong? Also, what I planned on doing with this is getting the basic syntax for one set of criteria because I have other "Customers" with a "Resp Time" of 5 minutes. What does the "i=2" above mean?

Thank you very much in advance for your help on this.

Robert.

 

[SkipVought]

Robert,

I don't understand why you have rejected using Conditional Formatting because you only stated ONE condition, UNLESS you have not stated the entire business case.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Robertislearning]

Hi Skip,

You're correct, I didn't state in my initial post that I have multiple conditions. I'm a novice at best when it comes to using Excel and am desperately searching for a way to minimize errors and time spent while pulling this daily report.

I'm thinking if I can atleast get the syntax down for just one condition, I'll be able to augment the code and add the other 5 or so conditions that I have.

Tks,
Robert.

 

[VBAjedi]

Back to your question - here's a bit of code that uses the Select Case approach (since you will have several conditions it should be efficient):

Sub Test1()
Dim ws As Worksheet, i As Integer
Set ws = Worksheets("Sheet1") ' or whatever your data sheet is named
i = 2 ' the row you want to start on
Do Until IsEmpty(ws.Cells(i, 1))
   Select Case ws.Cells(i, 3).Value
   Case 3 To 6
      With ws.Rows(i).Cells.Interior
          .ColorIndex = 6
          .Pattern = xlSolid
      End With
   Case Is > 6
      With ws.Rows(i).Cells.Interior
          .ColorIndex = 3
          .Pattern = xlSolid
      End With
   End Select
   i = i + 1
Loop
End Sub

Basically, this code makes sure there is a value in column 1 (ending when the first blank cell in column 1 is found), then checks the value in column 3. If the value is between 3 and 6 it highlights the row yellow, and if it is greater than 6 it highlights the row red.

Let me know if that does the trick!

VBAjedi

 

[Robertislearning]

VBAjedi,

Thank you so much for your input. I've spent the better part of this morning studying it. I was able to run the macro with your code and it worked. I do have two more questions as I begin to tailor the code to my actual report.

1) When it highlights a row, the entire row is highlighted indefinitely. Is there a way for me to truncate how far to the right the macro highlights? I'd like to be able to specify a certain column as the end point for the highlighting.

2) How do I prevent a certain row from being hilighted? These rows would be identified by the values in column 1. For example, in the above example of my report, how would I configure the code to prevent any row where the value in column 2 equals "SRV" from being highlighted?


Thanks again for your help, it is sincerely appreciated!

Robert.

 

[VBAjedi]

Both are easily accomplished.

1) This is smart programming, by the way. Formatting entire rows or columns makes the "Used Area" of your sheet much bigger, and the file size jumps too. To only highlight, say, columns A-Z, replace:

With ws.Rows(i).Cells.Interior

with

With ws.Range("A" & i & ":Z" & i).Interior


2) Insert the following line after the "Do" statement:

If Ucase(ws.Range(&quot;A&quot; & i).Value) <> Ucase(&quot;ValueToAvoid&quot Then

and insert an &quot;End If&quot; statement on the line before the Loop statement. Anytime the value in column &quot;A&quot; does not match the &quot;Value to avoid&quot;, the code between &quot;If&quot; and &quot;End If&quot; will be executed. The Ucase commands are optional: they convert both values to uppercase so that, for example, &quot;value1&quot; will match &quot;Value1&quot;.

Let me know if that isn't clear.


VBAjedi

 

[Robertislearning]

VBAjedi,

Item #1 above worked perfectly! With Item #2, when I run the macro it just seems to hang. I get the hourglass, and the first few conditions work properly, but it seems as if when it gets to the first Value that I want to exclude it just hangs. This goes on for several minutes and I do a <CTL> Pause/Break, then click on Debug and it highlights the &quot;End If&quot; statement before the Loop statement. Here is a copy of the code so far:


Sub Test1()
Dim ws As Worksheet, i As Integer
Set ws = Worksheets(&quot;Sheet1&quot ' or whatever your data sheet is named
i = 6 ' the row you want to start on
Do Until IsEmpty(ws.Cells(i, 1))
If UCase(ws.Range(&quot;B&quot; & i).Value) <> UCase(&quot;Support Voice Mail&quot Then
Select Case ws.Cells(i, 5).Value
Case 3 To 15
With ws.Range(&quot;A&quot; & i & &quot;:F&quot; & i).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Case Is > 15
With ws.Range(&quot;A&quot; & i & &quot;:F&quot; & i).Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End Select

Select Case ws.Cells(i, 4).Value
Case 3 To 15
With ws.Range(&quot;A&quot; & i & &quot;:F&quot; & i).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Case Is > 15
With ws.Range(&quot;A&quot; & i & &quot;:F&quot; & i).Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End Select

i = i + 1

End If
Loop


Also, as I understand it this code goes through and highlights any line where the value in column 5 is greater than 3 in yellow. Any line where the value in column 4 is greater than 15 gets highlighted in red. How would I configure this code to have one &quot;Cust&quot; only be highlighted in yellow if the value in columns 5 & 4 is 6 minutes?


Again, I can't really thank you enough for the time you've spent with me on this. After this is complete, I'd like to send you a small gift as a sign of my gratitude. Completing this will save me time which is a very scarce resource with me.

Rob.

 

[VBAjedi]

D'oh! So sorry - I should have told you to put the &quot;End If&quot; before the &quot;i = i + 1&quot;. What's happening right now is that it's running fine until the If condition is False (i.e. the two values match), at which time it keeps checking the same row over and over (because i isn't incremented!).

As far as the Case conditions go. . . not quite sure what you're asking. Currently, if a row passes your &quot;If&quot; test, your code looks at the value in column 5. If it is between 3 and 15, it highlights columns A-F of the row yellow; if it is greater than 15, it highlights columns A-F of the row red.
THEN your code looks at the value in column 4. If it is between 3 and 15, it highlights columns A-F of the row yellow; if it is greater than 15, it highlights columns A-F of the row red.

So right now any highlighting triggered by the value in column 4 will override any highlighting triggered by the value in column 5. Is this what you want?

Perhaps you should describe exactly what you are after (using the same approach I have just used) so I can give you the exact Case structure you need.


VBAjedi

 

[punchedin]

VBA Jedi
Great code. I used this to highlight based on text conditions and it wroked perfectly.

Thanks
Punchedin