Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Sorting and conditional formatting Results

Hello,
I am trying to do sorting and conditional formatting of two tables. They are presented in the atached example in sheet "Results". In short, with the help of several comboboxes, data from sheet "Source data" is filtered and copied in sheet "Results". So this is where we start from. Already copied ranges in "Results" should be sorted and formatted conditionally. Keep in mind that the number of rows and the data will vary according to the change in comboboxes.
Goals for the sorting:
1. The left table should be sorted by its last column (Growth N), the right table should be sorted by its last column (Delta N). Sort type - descending.

Goals for the conditional formatting:
1. Conditional formatting should be applied to the already sorted tables.
2. The rows of left and right table should be colored according to the following rules.
3. Formatting should be applied independently to the left and right table.
4. Top 25% of the rows should be colored in green (except headings). Last 25% of the rows should be colored in red. The middle 50% of the rows should be colored in orange.
5. If the number of the rows in the top 25% rows is not integer, then apply round function . If the result is round up, then increase the number of the rows in the top and last 25% and decrease them in the 50%. (For exmple, with 7 rows, top 25% - 2 rows, middle 50% - 3 rows, last 25% - 2 rows. If the result is round down, then increase the rows in the middle 50%. (For example, with 5 rows, top 25% - 1 row, middle 50% - 3 rows, last 25% - 1 row). But with 6 rows I suppose it is fair to add more rows to the middle 50%, despite that the round is up.
6. If we have only one row to format, let's make it green. If we have two rows, the first is green, the second is red. If we have three rows, then the first is green, the second is orange and the third is red.

If you have any suggestions, you are welcomed.

Thank you

I haven't seen anyone come close to this question, but is there a way to get
a sheet to sort by conditional formats I've set up. For instance, I've used
Green, Yellow, and Red. Is there an easy way to get it to sort it just by
the color, and not by the numbers contained in the cells?


Excel 2003

Ok so I've been working on this "HUGE" Inventory spreadsheet.

What I have is a Validation List in C1.

So I asked the Sheet to check and every time anything is updated in Column 3 to Sort and perform conditional formatting.

These to functions work when I run together in one Macro. Except the Check Column 3 is not there.

Anyway, here's the code. I suspect that my Target.Column function is wrong, but I don't know how to fix it.

Private Sub Worksheet_Change(ByVal Target As Range)

'Check that cell changed was in col C. If not, exit.
If Target.Column = 3 Then
    
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:J65536").Select
    Selection.Sort Key1:=Range("H3"), Order1:=xlAscending, Key2:=Range("F3") _
        , Order2:=xlDescending, Key3:=Range("G3"), Order3:=xlDescending, Header _
        :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
        , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
    Range("C1").Select

    Dim cel As Range
    Dim FormatRange As Range

    Set FormatRange = Range("H3:H65536")
    
    For Each cel In FormatRange
    Select Case cel.Value
    Case vbNullString
    cel.Interior.ColorIndex = xlNone
    Case "Out of Stock"
    cel.Interior.ColorIndex = 6
    Case "None on Hand"
    cel.Interior.ColorIndex = 43
    Case Is < 0.75
    cel.Interior.ColorIndex = 3
    Case 0.75 To 1
    cel.Interior.ColorIndex = 45
    Case Is > 1
    cel.Interior.ColorIndex = 41
    End Select
    Next cel

End If
End Sub


Good Morning All,
I am an elementary school teacher and I need help. We are required to keep a large amount of test data on students. Up to now we have been hand keying the data. The data comes to us as an excel file from a form scanner. The data has 3 fields (columns) of concern. NAME, ID NUMBER, & SCORE % but the data are not in any set order (yes - all names are in the name column etc. but they are not sorted initially) as the raw answer sheets are scanned in the order they are taken up (random). I would like to keep a master sheet that displays the outcome by student of each test... there are 7 interim assessments given prior to High Stakes testing... so the master sheet will eventually contain the results of 7 tests. As the data file for each test is given to me I need to import (bring into the master) then sort it to match the master and put that data into a new column. As one other requirement... the data is conditionally formatted to reflect by color code 4 different score zones into which the students fall.
I have no idea where to start... I can find my way around an Excel workbook etc. but I am a newbie. All my attempts to combine others macros etc have failed so I am coming to the experts. Thanks in advance for taking the time to read this.
Regards,
Rubin

Happy Thanksgiving To ALL!!

I have in A1:A100 names
In B1:P100, I have conditional formatting. However when I sort, the conditional formatting does not follow the referenced name cell. I've highlighted the range, then sorted using Data/Sort/Column. Anyone have any ideas?

Hi all

This is a follow-on from a previous thread, but to keep things clean, I thought I'd start a new one.

I'm trying to use the following function to generate a number which drives the conditional formatting in a 7th cell. This is my code so far :-

Code:
= 0.001 And p3 = 0.001 And p1 = 0.001 And p2 = 0.001 And p1 = 0.001 And p3 = 0.001 And p2 = 0.001 And p3

Hopefully you guys will be able to help with these; I think the first should be fairly simple. How do I incorporate wild cards in to conditional formatting? Usually you can use a * as part of and expression to denote that it could be several different items is possible to use it in conditional formatting. The other question is I have been reading about trying to sort by conditional formatting. I realize there is no real way to do it, and have read the post about using functions to find return the color of text or the background. I have tried to use those functions to sort with but they do not return the color of the conditional formatting, is there something else I can try. I am trying to avoid putting all of the formulas in cells and then sort by that.

Hello,

I have a very big spreadsheet that is sorted by customer name. I have it
subtotaled by using Data>Subtotals. I want to bold each of the subtotal
values. In the example below, that would be the number, "6.00".
Ex:

ABC Co. 2.00
ABC Co. 4.00
Subtotal ABC Co. 6.00

I tried using Conditional Formatting to say bold every number with the
formula, "=SUBTOTAL(9,D910)" b/c the #s that Excel subtotals, like the
"6.00" in the ex: above, have a formula in the cell with "=SUBTOTAL(...)".
It's not working.
The spreadsheet is too big to go through manually and bold the subtotals so
I need some kind of formula to put in the 'Formula is:' box of the
Conditional Formatting dialog box to do this for me.

Can someone help?

Thank you very much,
Studebaker

P.S. Thank you, Max for the help on my other conditional formatting question

I am familiar with conditional formatting in Excel--however, one of the
most useful formatting options would be to create "handwritten" (i.e.
not perfectly symmetrical) circles around certain data (say, over an
amount).

I know how to set up conditional formatting, but am unaware of any
formatting option other than font or cell (background) formatting. Is
there anyway to make ellipses show up if a value meets the criteria?

Currently, I've been using the old drawing tool to draw circles around
desired values (and copying the ellipse onto subsequent desired
values). However, this takes way too long (on large worksheets)--and
furthermore, the perfectly symmetric ellipses don't provide the level
of highlighting necessary.

Please advise if there's a way to do this sort of conditional
formatting.

thanks
gt

--
UWHusky
------------------------------------------------------------------------
UWHusky's Profile: http://www.excelforum.com/member.php...o&userid=31910
View this thread: http://www.excelforum.com/showthread...hreadid=516420

I have a spreadsheet with 62,000 lines of data. The first column is and Assessor's Parcel Numbers (APN). A small number of APN's are duplicated and I am trying to identify them, and sort them. When I use conditional formatting on the APN column, changing the cell color of duplicate cells, and then try to sort, Excel 2007 hangs up, and is shown as "not responding in the Task Manager". I have tried this on three different machines, with the same result.

One of the machines has a Pentium Core2Duo E7200 Processor with 4 gigabytes of RAM.

Any idea what is going on?

I have a project sheet that I am wanting to use to arrange projects by priority. My 5 priorities are "N/A, Done, Low, Med., High". These priorities are available in a drop down list and show up color coded when selected. That part...I've got. I want to be able to sort without using a custom sort. Using a custom sort would require everyone else to learn to use custom sort...and that is more difficult than anything excel has to offer. Lol.

I want to sort A-Z or Z-A and have these values fall in by priority. The way I would imagine it to work is that "N/A" = 1, "Done" = 2, "Low" = 3, "Med." = 4, "High" = 5

The question would be: How do I make my sheet sort by #'s 1-5 but show "N/A, Done, Low, Med., High"?

I am using Data Validation for my drop down list, using “List” and “source” =$A$132:$A$159 along with Conditional Formatting that changes different fill colors, depending on selection. Is there a way to use VBA with a command button that when selected would give the user a dialog box so that they could enter additional comment that would be added to existing list and would also be able to select a fill color for that new selection.

Below I have a recorded Marco to give an idea of what I would like to achieve, but of course using a input box of some sort to make selections of text and color
Also I'm using Excel 2007
Sub Macro1()
'
' Macro1 Macro
'

'
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, alertStyle:=xlValidalertStop, Operator:= _
        xlBetween, Formula1:="My Name"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Selection.FormatConditions.Add Type:=xlTextString, String:="My Name", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 4.99893185216834E-02
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 6214812
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub


I am familiar with conditional formatting in Excel--however, one of the most useful formatting options would be to create "handwritten" (i.e. not perfectly symmetrical) circles around certain data (say, over an amount).

I know how to set up conditional formatting, but am unaware of any formatting option other than font or cell (background) formatting. Is there anyway to make ellipses show up if a value meets the criteria?

Currently, I've been using the old drawing tool to draw circles around desired values (and copying the ellipse onto subsequent desired values). However, this takes way too long (on large worksheets)--and furthermore, the perfectly symmetric ellipses don't provide the level of highlighting necessary.

Please advise if there's a way to do this sort of conditional formatting.

thanks
gt

Hi,

I have three conditional formats on my spreadsheet:

1. =AND($F8="Smith
Street",$Y8="Yes",$H8>=14)

2. =AND($F8="Smith Street",$H8<=13)

3. =AND($F8="Smith Street",$Y8="No",$H8>=14)
I would like a formula that gives a text value to these three parametres for example if 1 is true letter a is shown. If 2 is true then letter b is shown and if 3 is true letter c is shown.

These would go into a hidden column and I would do a sorting macro on the basis of them thus sorting them based on the conditional formatting.

Is this at all possible, it would be greatly appreciated.

Thank you in advance,

JP

I am new to Excel and conditional formatting, but from reading threads and various sites I believe that the conditions have to be in a sort of reverse order.
The users input would be starting from Row 8.
Column A & B would be text and Columns C through to AH would be dates in the format 02-Feb-07.

I am after trying to do the the following:

Condition 1
If all cells in a row from Column A through to Column AH are not empty then make text white and backcolor black. (Only in column A would be ideal, but not 100% necessary)

Condition 2
If some cells are full and some cells are empty, make the empty cells backcolor Yellow. (Again, it would be nice to make this conditional that the cell in Column A had text in it)

Condition 3
I would like to conditionally format the cells Range(C8:AH1000), but until a row is used, by the user adding input through a form or onto the sheet, I would like to have the cells with no backcolor.

If I could meet this part of Condition 2:
it would be nice to make this conditional that the cell in Column A had text in it then condition 3 would not apply.

At the moment this is what I have for the 3 conditions:
Condition 1
then there is no backcolor
Condition 2
then Black backcolor and white text
Condition 3
empty cells backcolor = Yellow

At the moment this doesn't work, either if the complete row is fully used, or if the cells are empty.

Any pointers in the right direction would be great.

Thanks

..but with a twitch:

I have 50 names in colon A with 50 values in colon B, all in MAIN sheet, that I get by referencing to appropriate colon in one of 50 other sheets that are refreshed via "get external data from web" option.

Where my problem lies is in bonus sheet i made, in which i want to preserve those values on daily basis. so, for day 12 i have separate colon in which i manually paste values from main sheet, for day 13 also, and so on.

I WANT to, well, sort of, conditionally format every colon (apart from first) with a set of rules like:

IF day 13 cell is by 4 to 6 larger than appropriate one in colon 12, COLOR IT RED
IF day 14 cell is by 6 to 10 larger than appropriate one in colon 13, COLOR IT GREEN
IF day 15 cell is by 10 to 25 larger than appropriate one in colon 14, COLOR IT YELLOW

i have a really large table there, so i want to automate it as much as possible.

P.S. It has been a long time since i asked a question here, but i remember this forum as a great and helpfulll comunity, so please, i need help fast, i'm stuck in a infinite loop.

Hi folks,

Wonder if anyone can help here... I've attached a workbook to highlight my issue. It basically shows groups of 3 rows which contain data on indivudual cases, stored here as a tracker - a sort of database where we can keep track of the status of each case as it moves through different stages of production etc.

You will see that each case consists of 3 lines of data - the middle line contains a cell with conditional formatting which is dependent upon the value of the cell immediately above. My problem is that when adding a new case I have been copying the three rows above, but the conditional formatting rule formula contains absolute references which I then need to change manually. I have tried using relative references, but it doesn’t seem to be possible with Icon Sets. Icon sets seem ideal for my application - basically highlighting depending on how close to the planned date the 'act' date is. I have no VBA code in this tracker although I do plan on adding functionality soon to add a new 3 line block for new cases below the last existing case etc. and automatically have all the formatting rules in for icon sets if this is at all possible.

Anyone have any ideas how I could add a new case but copy the formatting relatively so that the icon set rules always refer to the cell above the 'target' cell being modified?

Don't think this is toooo hard a problem just takes some getting your head round it! At the moment I have the following code:


	VB:
	
 Range) 
     ' Conditional Formatting for more than 3 conditions
    Dim rng As Range 
     ' Target is a range::therefore,it can be more than one cell
     ' For example,,someone could delete the contents of a range,
     ' or someone could enter an array..
    Set rng = Intersect(Target,
Range("E27:E34,G27:G34,I27:I34,K27:K34,M27:M34,O27:O34,Q27:Q34,S27:S34,U27:U34,W27:W34,Y27:Y34,AA27:AA34")) 
     
    If rng Is Nothing Then 
        Exit Sub 
    Else 
         
        Select Case rng.Value 
        Case 0 
            rng.Interior.Color = RGB(0, 0, 0) 
            rng.Font.Color = RGB(255, 255, 255) 
            rng.Offset(0, -1).Font.Color = RGB(255, 255, 255) 
            rng.Offset(0, -1).Interior.Color = RGB(0, 0, 0) 
        Case 1 
            rng.Interior.Color = RGB(40, 40, 40) 
            rng.Font.Color = RGB(255, 255, 255) 
            rng.Offset(0, -1).Font.Color = RGB(255, 255, 255) 
            rng.Offset(0, -1).Interior.Color = RGB(40, 40, 40) 
        Case 2 
            rng.Interior.Color = RGB(65, 65, 65) 
            rng.Font.Color = RGB(255, 255, 255) 
            rng.Offset(0, -1).Font.Color = RGB(255, 255, 255) 
            rng.Offset(0, -1).Interior.Color = RGB(65, 65, 65) 
        Case 3 
            rng.Interior.Color = RGB(88, 88, 88) 
            rng.Font.Color = RGB(255, 255, 255) 
            rng.Offset(0, -1).Font.Color = RGB(255, 255, 255) 
            rng.Offset(0, -1).Interior.Color = RGB(88, 88, 88) 
        Case 4 
            rng.Interior.Color = RGB(116, 116, 116) 
            rng.Font.Color = RGB(255, 255, 255) 
            rng.Offset(0, -1).Font.Color = RGB(255, 255, 255) 
            rng.Offset(0, -1).Interior.Color = RGB(116, 116, 116) 
        Case 5 
            rng.Interior.Color = RGB(139, 139, 139) 
            rng.Font.Color = RGB(255, 255, 255) 
            rng.Offset(0, -1).Font.Color = RGB(255, 255, 255) 
            rng.Offset(0, -1).Interior.Color = RGB(139, 139, 139) 
        Case 6 
            rng.Interior.Color = RGB(167, 167, 167) 
            rng.Font.Color = RGB(255, 255, 255) 
            rng.Offset(0, -1).Font.Color = RGB(255, 255, 255) 
            rng.Offset(0, -1).Interior.Color = RGB(167, 167, 167) 
        Case 7 
            rng.Interior.Color = RGB(186, 186, 186) 
            rng.Font.Color = RGB(0, 0, 0) 
            rng.Offset(0, -1).Font.Color = RGB(0, 0, 0) 
            rng.Offset(0, -1).Interior.Color = RGB(186, 186, 186) 
        Case 8 
            rng.Interior.Color = RGB(211, 211, 211) 
            rng.Font.Color = RGB(0, 0, 0) 
            rng.Offset(0, -1).Font.Color = RGB(0, 0, 0) 
            rng.Offset(0, -1).Interior.Color = RGB(211, 211, 211) 
        Case 9 
            rng.Interior.Color = RGB(234, 234, 234) 
            rng.Font.Color = RGB(0, 0, 0) 
            rng.Offset(0, -1).Font.Color = RGB(0, 0, 0) 
            rng.Offset(0, -1).Interior.Color = RGB(234, 234, 234) 
        Case 10 
            rng.Interior.Color = RGB(255, 255, 255) 
            rng.Font.Color = RGB(0, 0, 0) 
            rng.Offset(0, -1).Font.Color = RGB(0, 0, 0) 
            rng.Offset(0, -1).Interior.Color = RGB(255, 255, 255) 
        Case Else 
            rng.Interior.Color = RGB(128, 128, 128) 
            rng.Font.Color = RGB(0, 0, 0) 
            rng.Offset(0, -1).Font.Color = RGB(0, 0, 0) 
            rng.Offset(0, -1).Interior.Color = RGB(128, 128, 128) 
        End Select 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This works fine but I'll use an example to say what I need next...

At the moment if I type 0 into cell I33, cells I33 and H33 turn black, and their text turns white. All good. But I would like it to be so that if cell H33 has "y" in it the code above runs, but if it has "n" in it the code above doesn't run but both H33 and I33 are filled white.

Hope that makes some sort of sense!

Any help appreciated

George

Quite simply, I would like to sort and filter names and dates (in specified colors given the specified dates).

What has taken me four days to figure out will likely take someone in this forum seconds.
I am very new to all of this, and I come modestly, respectfully, and appreciative for any help.
I have a few questions, but they are all connected, and with a few short answers I can have my entire sheet functioning beautifully.

Conditional formatting is the most simple way to do this. However, I have a =ColorFunction sub that conditional formatting would interfere with (because it is my understanding the “=ColorFunction” does not recognize colors highlighted conditionally (or with private subs either). For this reason, I have thrown together an alternative sub macro that creates the same result that my colorfunction formula could recognize.


	VB:
	
 Highlight_Date_Today_Red() 
     'searches finds and highlights today date in range in a specified color without the use of standard conditional
formatting
     ' Highlight_Date_Today_Red Macro
     
    Range("E4:E1000").Select 
    Application.FindFormat.Clear 
     'On Error Resume Next
    Cells.Find(What:=DateValue(Today), After:=ActiveCell, LookIn:=xlFormulas, _ 
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
    MatchCase:=False).Activate 
     
    With Selection.Interior 
        .Pattern = xlSolid 
        .PatternColorIndex = xlAutomatic 
        .Color = 255 
        .TintAndShade = 0 
        .PatternTintAndShade = 0 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It worked but it only highlighted one cell. Sad for me, and yes it is okay to laugh at my code (but at least give me credit for attempting this Frankenstein monstrosity) Lol

Here are my four questions:

How do I get it to search and highlight the current date today in through the entire column accordingly?

As for multiple values, how would I get it to highlight yesterday’s date in another the color the same way accordingly?

Next, how would I write this same code with something along the lines of:

If Range(“H4”) contains “/”, then highlight cell (“E4”) in this color?

Finally,

If Range(“H4”) contains no fill color, then highlight cell (“E4”) in this color?

Any help on this would be super extremely appreciated you have no idea. I tried so many variations from so many forums for so long that it’s time I sought help from someone. Anyone who can help me this will have a speedy response from me guaranteed. I will be monitoring any responses round the clock. Thanks in advance.

Quick note: I did make sure the dates I attempted to find and highlight were formatted as “date cells” and not “text cells”.

I am trying to create a macro with conditional formatting in excel. My spreadsheet contains a column of calculated scores. I have built a macro to sort the scores in descending order. Now, I want to build a macro that once my responses are in descending order it will take the number of responses and break out into 3 equal categories (by rank score) and shade the cell in 3 colors - - red, green, yellow. Can anyone help.


No luck finding an answer? You could always try Google.