Solved

VBA to change colors

Posted on 2012-03-14
4
461 Views
Last Modified: 2012-03-15
Hi Guys, what would be the code for

If Range(S6:S450) >=38.75 then VB red, else VB white

If (S6:S450) >=38.75 then Range(B6:B450)VB red, else VB white

I need the code to activate these 2 columns depending on Column S values

I did this with conditional format but it slows down my sheet operation.
0
Comment
Question by:rsen1
  • 2
  • 2
4 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 37723117
It's unlikely that VBA will be faster than Conditional Formatting.  Indeed, it would probably be slower to use VBA with a Change or Calculate event sub than to just use CF.
0
 

Author Comment

by:rsen1
ID: 37723123
Thank you for your response, but would you please help me with code all the same?
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 37723159
I still think that this is a bad idea, but if you insist, this goes in the sheet module for the worksheet you need to "watch":


Option Explicit

Private Sub Worksheet_Calculate()
    
    ApplyColors
    
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Me.[s6:s450]) Is Nothing Then
        ApplyColors Intersect(Target, Me.[s6:s450])
    End If
    
End Sub

Private Sub ApplyColors(Optional rng As Range)
    
    Dim UseThisRange As Range
    Dim cel As Range
    
    Const Threshold As Double = 38.75
    
    Application.EnableEvents = False
    
    If Not rng Is Nothing Then
        Set UseThisRange = rng
    Else
        Set UseThisRange = Intersect(Me.UsedRange, Me.[s6:s450])
    End If
    
    For Each cel In UseThisRange.Cells
        If cel >= Threshold Then
            cel.Interior.Color = vbRed
            Me.Cells(cel.Row, "b").Interior.Color = vbRed
        Else
            cel.Interior.ColorIndex = xlColorIndexNone
            Me.Cells(cel.Row, "b").Interior.ColorIndex = xlColorIndexNone
        End If
    Next
    
    Application.EnableEvents = True
    
End Sub

Open in new window



Keep the Change event if the values in S will change by manual entry, and the Calculate event if they will change by formula recalculation.
0
 

Author Comment

by:rsen1
ID: 37723188
Thank you very much
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question