VBA code to toggle ActiveCell.Interior.Color property

Rasm

Well-known Member
Joined
Feb 9, 2011
Messages
500
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
 
Last edited:

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Rasm

Well-known Member
Joined
Feb 9, 2011
Messages
500
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

Legend
Joined
May 15, 2003
Messages
29,536
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

Well-known Member
Joined
Feb 28, 2003
Messages
2,615

ADVERTISEMENT

Hi,

If you really need VBA, try

Code:
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,007
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

Well-known Member
Joined
Feb 9, 2011
Messages
500
Conditional formatting it is - thanks guys - good thing I have a forgiving wife - :LOL: -
 

Watch MrExcel Video

Forum statistics

Threads
1,109,368
Messages
5,528,274
Members
409,813
Latest member
robyrux

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top