Trying to move Conditional Formatting to VBA

[Aaron DOJ]

Hello Excel Masters!

I am creating a Template that will be used by many people, I am noticing (during my Alpha testing) that there is lag while entering data. Additionally, the users will have to insert rows in the existing fields and when that happens I am noticing some Conditional Formats issues that are happening. I have a lot of Conditional Formats and in an effort to reduce the lag and another issues I am thinking that having most of my Conditional Formatting in VBA will help.

The first thing I am trying to accomplish is if a cell in G10:G900 range is =1999 it will come out to a light blue (as well as any cell to the right (H10:AA900)). Best I can tell the command should look something like the below, but clearly I am missing something as it is not working:

Sub MultipleConditionalFormattingExample()
Dim MyRange As Range
'Create range object
Set MyRange = Range("H10:AA900")
'Delete previous conditional formats
MyRange.FormatConditions.Delete

MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:=G10: G900 = 1999       'ultimately this will be any year ending in an odd number
MyRange.FormatConditions(1).Interior.Color = RGB(189, 215, 238)

End Sub

Below is the Conditional Formatting Rule.

And below is the above working in excel.

Thank you in advance,
-Aaron

 

[kweaver]

Is this what you're looking for?

Sub AAronDOJ()
Dim lr As Long, i As Long
lr = Cells(Rows.Count, "G").End(xlUp).Row
For i = 10 To lr
 If Cells(i, "G") = 1999 Then
  Range("G" & i & ":AA" & i).Interior.Color = RGB(89, 215, 238)
 End If
Next i
End Sub

 

[Osvaldo Palmeiro]

Hi.
If user insert rows above row #900 then the Format Conditions will be extended accordingly. Eg. if two rows were inserted then FC is now applied until row #902.
If you don't want that and if there aren't others FC on range G:AA then uncomment the first row of the code in order to delete FC from that range ~~~> Columns("G:AA").FormatConditions.Delete

Sub MultipleConditionalFormattingExample()
 'Columns("G:AA").FormatConditions.Delete 'delete FC on columns G:AA
 With [G10:AA900]
  .FormatConditions.Delete 'not needed if deleted earlier
  .FormatConditions.Add Type:=xlExpression, Formula1:="=$G10=1999"
  .FormatConditions(1).Interior.Color = RGB(189, 215, 238)
 End With
End Sub

 

[Aaron DOJ]

Is this what you're looking for?

Sub AAronDOJ()
Dim lr As Long, i As Long
lr = Cells(Rows.Count, "G").End(xlUp).Row
For i = 10 To lr
 If Cells(i, "G") = 1999 Then
  Range("G" & i & ":AA" & i).Interior.Color = RGB(89, 215, 238)
 End If
Next i
End Sub

Thank you for the assistance in the code. I tried it and it does work, however, I am not familiar with a lot of the code format you provided. Specifically, how is
lr = Cells(Rows.Count, "G").End(xlUp).Row
For i = 10 To lr
working?
How would I go about adding more years to that code?
Is there something I could do that would say if any cell text in range G10:G900 ends with 1,3,5,7,9 then RGB(189, 215, 238)?

 

[Aaron DOJ]

Hi.
If user insert rows above row #900 then the Format Conditions will be extended accordingly. Eg. if two rows were inserted then FC is now applied until row #902.
If you don't want that and if there aren't others FC on range G:AA then uncomment the first row of the code in order to delete FC from that range ~~~> Columns("G:AA").FormatConditions.Delete

Sub MultipleConditionalFormattingExample()
 'Columns("G:AA").FormatConditions.Delete 'delete FC on columns G:AA
 With [G10:AA900]
  .FormatConditions.Delete 'not needed if deleted earlier
  .FormatConditions.Add Type:=xlExpression, Formula1:="=$G10=1999"
  .FormatConditions(1).Interior.Color = RGB(189, 215, 238)
 End With
End Sub

Thank you, I do have something similar to that already, not sure if I will turn it on or not. As I stated before there is a lot of Conditional Formatting I am trying to move over to VBA and I am not sure if I can move it all and make it work. So I might need to make sure it does not delete anything I could not get into VBA. Specifically, a select few rows have the following and I need to ensure it stays in only that cell, I have 51 of those below. However, if a user adds a row, say at row 10, then code needs to change to =AND($S22<>$S24,$S22<>0) (AKA move down one to adjust)....any ideas on how to go about doing that?

 

[kweaver]

Thank you for the assistance in the code. I tried it and it does work, however, I am not familiar with a lot of the code format you provided. Specifically, how is
lr = Cells(Rows.Count, "G").End(xlUp).Row
For i = 10 To lr
working?
How would I go about adding more years to that code?
Is there something I could do that would say if any cell text in range G10:G900 ends with 1,3,5,7,9 then RGB(189, 215, 238)?

The variable lr is the "last row" of data in the G column. The routine then loops through rows 10 to the last row and sets the interior color if the cell contains 1999.
Are you now saying that the cells in the G column contain TEXT rather than numbers and the text (possible could) end with a number rather than test for the number 1999?
If so, you could change the test to:

 If Right(Cells(i, "G"), 1) Mod 2 = 1 Then

If that's what you mean.

 

[Aaron DOJ]

The variable lr is the "last row" of data in the G column. The routine then loops through rows 10 to the last row and sets the interior color if the cell contains 1999.
Are you now saying that the cells in the G column contain TEXT rather than numbers and the text (possible could) end with a number rather than test for the number 1999?
If so, you could change the test to:

 If Right(Cells(i, "G"), 1) Mod 2 = 1 Then

If that's what you mean.

Column G is actually a dropdown list, and if I understand it correctly once a selection is selected it is considered text?

 

[kweaver]

Column G is actually a dropdown list, and if I understand it correctly once a selection is selected it is considered text?
View attachment 40847

It's not necessarily text depending on the table from which you are selecting. In this case, the choice is from a list of numbers. You can use =ISNUMBER(G10) for example to see that's the case.

So, if you're trying to test for even or odd years you don't need to use the RIGHT function. Just use:

If Cells(i, "G") Mod 2 = 1 Then

 

[Aaron DOJ]

It's not necessarily text depending on the table from which you are selecting. In this case, the choice is from a list of numbers. You can use =ISNUMBER(G10) for example to see that's the case.

So, if you're trying to test for even or odd years you don't need to use the RIGHT function. Just use:

If Cells(i, "G") Mod 2 = 1 Then

Fantastic! that has all of the correct rows (odds) highlighted...so this is what I have atm:

Sub MultipleConditionalFormattingExample()
Columns("G:AA").FormatConditions.Delete 'delete FC on columns G:AA
With [G10:AA900]
.FormatConditions.Delete
End With
Dim lr As Long, i As Long
lr = Cells(Rows.Count, "G").End(xlUp).Row
For i = 10 To lr
If Cells(i, "G") Mod 2 = 1 Then
Range("G" & i & ":AA" & i).Interior.Color = RGB(189, 215, 238)
End If
Next i
End Sub

Any thoughts on how to do post/remark #5 above?