Conditional Formating - trafic lights

[Jana.Luo]

Hi all,

I wanted to use 3traffic lights to do the conditional formatting for my data.

e.g:

25
21%
30
56%
51
48%
11

I only want to apply the rule to 21%,56% and 48%, not the others. But if i highlight the whole column, the rule would apply to all the data.

How can I do that correctly?
One way I can think about was I need to highlight those three % numbers instead highlight the whole column. But what if the numbers are too many?

Need your ideas! Thanks in advance!

Jana

 

[Jana.Luo]

Yes, that was I am after.

But thanks all of you for your big effort!

 

[tusharm]

Suppose your data are in B starting with B2. Then, in C2 (assuming C is empty), enter the formula =IF(OR(CELL("format",B2)="P0",CELL("format",B2)="P2"),B2,"") This gives you the value in B if the format in B is 0% or 0.00%. Otherwise, C2 contains the zero length string ""

Copy C2 as far down as you have data in B.

Now, set conditional formatting for C for a 3 icon set with *numeric* thresholds (not percent thresholds) of 0.67, 0.33, and 0.

The icon is in C not B. Hopefully, that will work just fine.

Hi Lenze,

First, I have more than 5000 rows which is too hard to select the % numbers manually.

Second, the pattern is not always list the example you saw...

But do thank you for the suggestion!

Jana

 

[AusSteelMan]

OK. I have had a try, but it doesn't work yet.

I based the following code on a code I already had (with help from here I'm sure)

The original code selected all unlocked cells - as shown by the first line of comment text in the For loop.

As you can see, I have tried some alternatives to be able to select only cells that have been formatted as a percentage.

I am having no joy though.

The only thing that is working is the "General" code below, and only if all cells in the range are General (I thought this would happen since I started to refer to the "WorkRange" only, not the cells inside it. I tried to alter this by adding Cells to the code, but again unless all cells were General, it returned the MSG box)

Sub SelectPercentFormatCell()
'this does not work yet
 
    Dim WorkRange As Range
    Dim FoundCells As Range
    Dim Cell As Range
    Set WorkRange = ActiveSheet.UsedRange
    For Each Cell In WorkRange
        'If Cell.Locked = False Then  ''original code to check for locked status
        'If WorkRange.SpecialCells(xlCellTypeSameFormatConditions).NumberFormat = "Percent" Then
        'If WorkRange.NumberFormat = "Percent" Then
        'If WorkRange.NumberFormat = "Percentage" Then
        If WorkRange.NumberFormat = "General" Then
        'If WorkRange.Cells.NumberFormat = "General" Then
            If FoundCells Is Nothing Then
                Set FoundCells = Cell
            Else
                Set FoundCells = Union(FoundCells, Cell)
            End If
        End If
    Next Cell
    If FoundCells Is Nothing Then
        MsgBox "No cells are %."
    Else
        FoundCells.Select
    End If
End Sub

Note: If the line of code that contains "SpecialCells" is the active line, I get a "Run-Time Error 1004: No cells were found" error. All the other lines return the MSG Box (except the "General" code)

Anyone got any ideas to help me and the OP?

(I'm trying to learn and help where I can)

Thanks,
ASM

 

[tusharm]

One way you can learn what to code is to use Excel VBE help. Does SpecialCells support a format option? Does Find?

To get the correct specification for a number format of %, use the macro recorder to specify a format of %. Now, you will know what to look for!

But, even more important than the 2 above tips, see if Excel will do the work for you. Then, you don't have to reinvent the wheel. Developing a VBA solution is almost always the most expensive approach. Not only do you have to make a serious commitment to developing, testing, and fine-tuning the code, you also impose on the person you are helping a maintenance headache not to mention that the people using your solution will have to deal with constant "This file contains macros" warnings.

OK. I have had a try, but it doesn't work yet.

I based the following code on a code I already had (with help from here I'm sure)

The original code selected all unlocked cells - as shown by the first line of comment text in the For loop.

As you can see, I have tried some alternatives to be able to select only cells that have been formatted as a percentage.

I am having no joy though.

The only thing that is working is the "General" code below, and only if all cells in the range are General (I thought this would happen since I started to refer to the "WorkRange" only, not the cells inside it. I tried to alter this by adding Cells to the code, but again unless all cells were General, it returned the MSG box)

Sub SelectPercentFormatCell()
'this does not work yet
 
    Dim WorkRange As Range
    Dim FoundCells As Range
    Dim Cell As Range
    Set WorkRange = ActiveSheet.UsedRange
    For Each Cell In WorkRange
        'If Cell.Locked = False Then  ''original code to check for locked status
        'If WorkRange.SpecialCells(xlCellTypeSameFormatConditions).NumberFormat = "Percent" Then
        'If WorkRange.NumberFormat = "Percent" Then
        'If WorkRange.NumberFormat = "Percentage" Then
        If WorkRange.NumberFormat = "General" Then
        'If WorkRange.Cells.NumberFormat = "General" Then
            If FoundCells Is Nothing Then
                Set FoundCells = Cell
            Else
                Set FoundCells = Union(FoundCells, Cell)
            End If
        End If
    Next Cell
    If FoundCells Is Nothing Then
        MsgBox "No cells are %."
    Else
        FoundCells.Select
    End If
End Sub

Note: If the line of code that contains "SpecialCells" is the active line, I get a "Run-Time Error 1004: No cells were found" error. All the other lines return the MSG Box (except the "General" code)

Anyone got any ideas to help me and the OP?

(I'm trying to learn and help where I can)

Thanks,
ASM

 

[AusSteelMan]

I was just about to post the following when you posted.

I have found that the format name for a percentage cell is "0%" so I added this line

If WorkRange.NumberFormat = "0%" Then

and it works if all cells are percent.

I will look again in help to see what I may have missed. I like to look at some of these problems so I can tuck some info away in my head should I ever need to do something similar.

Thanks again.
Darren