UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Vb Conditional Formatting In Excel 2010    
 
   
annalyst57
post Jun 17 2016, 02:23 PM
Post#1



Posts: 9
Joined: 25-February 15



Hi I used this code for other conditional formatting, but this time I want to change color of the cells based on percentages.
I know I can do this using the basic tools but I have multiple workbooks to incorporate this in and wanted to save a little time especially if there are updates or changes that occur.
This will highlight the range I'm looking at but it is a hit and miss on the correct cells.
I'm looking to highlight just yellow for now to get it working and if any cell is >5% or <-5% I want it to turn yellow, I plan on adding to this for more conditions.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

For Each A In Worksheets("XX xxx").Range("M8:M229")

If (A.Value) >="5.0%" Or (A.Value) <="-5.0%" Then
A.Interior.Color = RGB(255, 255, 0)

ElseIf (A.Value) = "" Then
A.Interior.Color = RGB(255, 255, 255)

End If
Next
End Sub

Any suggestions?
Thanks Mary
Go to the top of the page
 
Daniel_Stokley
post Jun 17 2016, 03:15 PM
Post#2



Posts: 246
Joined: 22-December 14
From: Grand Junction, CO, USA


Hi,

Try changing the order of your tests. I found that empty cells were not working right. Also, I'm not sure about using "5.0%" instead of just 0.05. Maybe you can take the following and make any necessary changes.
CODE
Sub CustomConditional()
    Dim A As Range
    For Each A In Worksheets("XX xxx").Range("A1:E10")
'        Debug.Print A.Column & ":" & A.Row & ":" & A.Value & "|"
        If A.Value = Empty Then
            A.Interior.Color = RGB(255, 0, 255)
        ElseIf (A.Value) >= 0.05 Or (A.Value) <= -0.05 Then
            A.Interior.Color = RGB(255, 255, 0)
        Else
            A.Interior.Color = RGB(0, 255, 255)
        End If
    Next
End Sub
Go to the top of the page
 
annalyst57
post Jun 17 2016, 03:56 PM
Post#3



Posts: 9
Joined: 25-February 15



I must be missing something, it didn't do anything.

But I did try the 0.05 instead before in my other code and it didn't make much of a difference.
Go to the top of the page
 
annalyst57
post Jun 17 2016, 03:59 PM
Post#4



Posts: 9
Joined: 25-February 15



What I did do was remove the "" from the 0.05 from my original code and it works.......yeah


Thank you
Go to the top of the page
 
ADezii
post Jun 17 2016, 06:03 PM
Post#5



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


Format the Range M8:M229 as Percentage, then (in Range, out of Range, Empty):
CODE
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Excel.Range

For Each rng In Worksheets("Sheet1").Range("M8:M229")
  If (rng.Value) >= 0.05 Or (rng.Value) <= -0.05 Then
    rng.Interior.Color = RGB(255, 255, 0)
  ElseIf rng.Value = "" Then
    rng.Interior.Color = RGB(0, 0, 255)
  Else      'rng.Value < 0.05 OR rng.Value > -0.05 AND rng.Value <> ""
    rng.Interior.Color = RGB(255, 0, 0)
  End If
Next
End Sub
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 08:31 PM