Free Microsoft Excel 2013 Quick Reference

- Sorting and conditional formatting
- Sort by conditional formats?
- VB Script Sorting and Conditional Formatting?
- Merging, sorting, and conditional formatting
- Conditional Formatting and Sorting
- Sorting out conditional formatting
- Two Conditional Formatting Questions
- Subtotals and Conditional Formatting
- Conditional Formatting: "handwritten" circles?
- Sorting on conditionally formatted cell color, Excel 2007 hangs
- Formula Based Cell Sort with conditional formatting and data validation
- Data Validation and Conditional Formatting using VBA
- Conditional Formatting: "handwritten" circles?
- Formula to enable sorting of conditional formatting
- [RESOLVED]3 Condition Conditional Formatting
- Conditional Format as I paste values
- Conditional formatting with Icon Sets and relative references
- VBA Conditional Format
- Set cell color for cells matching certain properties instead of Conditional Format
- Conditional Format & Sort Macro For Ranking

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

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

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

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?

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

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

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

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

Any idea what is going on?

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"?

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

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

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

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.

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?

VB:This works fine but I'll use an example to say what I need next...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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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: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) LolHighlight_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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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”.