Conditional formatting with vba

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:20 PM GMT

Question

QUESTION: I have a spreadsheet created in2002 and I want to change the background colour of the whole row to gray if there is a date in column AA. This needs to be done from row 20 onwards.

Normally, I would use conditional formatting to do this, but my problem is that columns A, K and L already have the maximum 3 conditional formats applied to change their background colour based on the date in column K. (Note if there is a date in column AA, then none of the 3 conditional formats is being activated.)

I have read that you can use VBA to add more conditional formats but can not find the correct code to do what I need it to do (and that is explained in simple terms!).

I look forward to hearing from you.

Kind Catherine

ANSWER: Catherine/Kate,

VBA doesn't really add conditional formatting conditions per se. You can write code to act like conditional formatting. I have written a change event routine. This routine if placed in the sheet module of the sheet where you want the behavior, will run anytime a cell or cells is edited. It loops through column AA from row 20 downward and if it finds a date in a cell it colors that row Gray.

Right click on the sheet tab and select view code. then paste in this code:

this is slightly revised to declare variables in case you are configured to require that. Also removes the debug.print command which is unnecessary.

Private Sub Worksheet_Change(ByVal Target As Range) Dim r as Range, cell as Range Set r = Range("AA20", Cells(Rows.Count, "AA").End(xlUp))

If r(1).Row < 20 Then Exit Sub For Each cell In r

 If Len(Trim(cell)) > 0 Then
   If IsDate(cell) Then
      cell.EntireRow.Interior.ColorIndex = 15
   Else
      cell.EntireRow.Interior.ColorIndex = xlNone
   End If
 End If

Next End Sub

Make sure macros are enabled.

I would test this on a copy of your workbook until you are sure it does what you want.

Post back with any questions/problems.


---------- FOLLOW-UP ----------

QUESTION: Many I've been struggling with this for days now!

At first, your code seemed to work very well, but I've found a couple of minor problems:

1) If the date is removed from column AA, the row remains grey instead of reverting to its previous state;

2) I have protected the spreadsheet to avoid users accidentally removing the many formulas that are in there (ideally, they should only be allowed to select locked cells, select unlocked cells and use the autofilter). However, now when I put a date in column AA, I now get the following error message:

Run-time error '1004': Unable to set the ColorIndex property of the Interior class

Is there a solution to this?


Kate

ANSWER: Kate,

try this. Change the password ("ABC") to your password (in two places)

I have also corrected it so the gray would be removed if the date is removed (one the macro has run).


Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range, cell As Range On Error GoTo ErrHandler Set r = Range("AA20", Cells(Rows.Count, "AA").End(xlUp)) If r(1).Row < 20 Then Exit Sub ' change password in next line *************** Me.Unprotect Password:="ABC" For Each cell In r

 If Len(Trim(cell)) > 0 Then
   If IsDate(cell) Then
      cell.EntireRow.Interior.ColorIndex = 15
   Else
      cell.EntireRow.Interior.ColorIndex = xlNone
   End If
 Else
    cell.EntireRow.Interior.ColorIndex = xlNone
 End If

Next ErrHandler: ' change password in next line ************* Me.Protect Password:="ABC" End Sub



---------- FOLLOW-UP ----------

QUESTION: This is much better. However, one small problem - when I put a date in column AA of the bottom row of data (for example, the current bottom row is row 60), then remove it, the line remains grey.

If I then put a date in a row lower down (e.g. row 61), then row 60 updates to remove the gray background but row 61 remains grey even if the date is removed.

Is there a way that this can be fixed?

Kind Kate

Answer

Kate,

Here is one way to fix it. The challenge is finding the bottom of your data. since I know nothing about your sheet, I will just clear from row 20 to the actual bottom of the worksheet and then put the gray rows back in where there is a date. If that turns out to make things to slow, then post back with what column I can check to find the last used row of your data (there won't be a date entered beyond this row) and get a consistent accurate result.

Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range, cell As Range On Error GoTo ErrHandler Set r = Range("AA20", Cells(Rows.Count, "AA").End(xlUp)) If r(1).Row < 20 Then Exit Sub ' change password in next line *************** Me.Unprotect Password:="ABC" Range("A20:A" & rows.count).EntireRow.Interior.colorIndex = xlNone For Each cell In r

If Len(Trim(cell)) > 0 Then
  If IsDate(cell) Then
     cell.EntireRow.Interior.ColorIndex = 15
  End If
End If

Next ErrHandler: ' change password in next line ************* Me.Protect Password:="ABC" End Sub

Advertisement

©2020 eLuminary LLC. All rights reserved.