VBA code to toggle ActiveCell.Interior.Color property

[Rasm]

I have a column that can be the value True or False - I would like to change the property

ActiveCell.Interior.Color = &HFFFFFF 'white - False

ActiveCell.Interior.Color = &H80FFFF 'Yellow - True

what is the most effective way to write code to do this (for like 20K rows)- Lets assume the column is "C" - My cells are initially populate from files read into arrays and then the arrays are transposed using the worksheetfunction. So I think the coloring should be done after my transpose code line

 

[Smitty]

Why not use Conditional Formatting?

 

[Rasm]

perfect as long as the code can reside outside of the sheet code - this is an add-in. So the user is using regular XLSX files - imports the data - manipulates the data - then saves it as regular files. I am not familiar with conditional formatting.

 

[Smitty]

I'd just record a macro applying the Conditional Formats you want to the column in question and add that to your code. CF will be a lot more efficient than looping through and evaluating each cell.

For the False condition you could use: =IF(AND(A1<>""),A1=FALSE)

For True you could use: Format Cells-->Equal to-->True

There's a good write up in the helpfile about CF, so I'd start there.

 

[Krishnakumar]

Hi,

If you really need VBA, try

Sub kTest()
Dim k, r  As Range, n As Long, i As Long
Dim Addr(1 To 1000) As String, s As String

Set r = Range("a2:a" & Range("a" & Rows.Count).End(xlUp).Row) 'adjust the range
k = r
For i = 1 To UBound(k, 1)
    If k(i, 1) Then
        s = s & "," & Cells(i, 1).Address(0, 0)
        If Len(s) > 245 Then
            n = n + 1
            Addr(n) = Mid$(s, 2)
            s = ""
        End If
    End If
Next
If Len(s) > 1 Then
    n = n + 1
    Addr(n) = Mid$(s, 2)
    s = ""
End If
If n Then
    r.Interior.Color = xlNone
    With r
        For i = 1 To n
            .Range(CStr(Addr(i))).Interior.Color = 65535
        Next
    End With
End If
End Sub

HTH

 

[tusharm]

Conditional Formatting is a powerful capability. I'd invest the time to learn it.

perfect as long as the code can reside outside of the sheet code - this is an add-in. So the user is using regular XLSX files - imports the data - manipulates the data - then saves it as regular files. I am not familiar with conditional formatting.

 

[Rasm]

Conditional formatting it is - thanks guys - good thing I have a forgiving wife - -