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

Free Microsoft Excel 2013 Quick Reference

Conditional Formating using percentages

I have attached a worksheet with the narrative which forms the basis of my problem.
I am required to use Conditional Formatting which will be looking at single cells at month end.
At the end of the month I want to show the conditional formatting tool used with a traffic light color system, which is defined by using percentages.
I commence with a value at the end of Month 1
At the end of Month 2 the average value if greater than Month 1 the cell should simply be Red
If the value at Month end 2 is less than 5% of the end of Month 1 the cell should be Orange
If the cell value at Month end 2 is greater than 5% of the value of Month end 1 the cell color should be green
The cells in qustion arte shown on the attached worksheet.
Thanks for your support. Mike


Post your answer or comment

comments powered by Disqus
Trying to complete conditional formatting across a range of cells comparing actual to plan

Here are the following rules I would like to use:

Plan - A1
Actual - B1

((B1-A1)/B1)

If actual percentage variance is <=0 fill B1 green
If actual percentage variance is >=0 <=0.05 fill B1 yellow
If actual percentage variance is >0.05 color fill B1 red

If you can help it would be appreciated.

Hello I'm working on Excel 2003, determining position vacancies for hiring purposes. In my sheet, I have Columns C, D and E with data.

Column C- Total # Positions (Control)
Column D- Total # Filled Positions
Column E- Vacancies

Columns C & D were manually filled, while Column E shows the formula =C5-D5. I would like to use the Conditional Formatting action to highlight all the data in Column E that is greater than or equal to a 15% vacancy with a different font color.

Seeing as how Column E was filled by a formula, is there any way a Conditional Format could work here?

Thank you!

Hi Everyone,

I have a spreadsheet I am working on for a prospective retail store. I am currently comparing the potential ROI of Renting vs. Owning.

In the spreadsheet, I have created a drop down list that reads
Mortgage
Rent

I would like to have a custom format in a different cell that conditionally will read:

"Rent:" #.00 "Per Sq. Ft."

or

"Mortgage:" #.00 "Per Sq. Ft."

Can the conditional formatting "Use a Formula to determine which cells to format" make the adjustments (I have already tried several ways to write the formula in conditional formatting with no success).

Any help would be appreciated

Hi im new here and I needed a little help. I am trying to create a Excel worksheet that uses conditional formatting using two dates in two columns ex: D2 has 2/8/2009 and E2 has 10/09/2010. What I want to do is set up where if E2 is greater or equal to 30 then I want the D2 to be green same for 60 which would be yellow and 90 would be red. Anything less than 30 I want to stay black. Thank you in advance

Good afternoon,

I am trying to work with conditional formatting using a macro.

How it works is I select the range I want to format manually then I run this macro.

It doesn't work

When I check the conditional format the formula is wrong. Instead of referencing the correct cell in columns E & F is is referencing E65282 & F65282

What do I need to change?


	VB:
	
 CondForm() 
     
    Selection.FormatConditions.Delete 
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G$1$F2" 
    Selection.FormatConditions(2).Interior.ColorIndex = 37 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks again

I know OpenOffice can do it but I can't seem to figure out if Excel can or not.

I have various styles defined to include the font, color, pattern, etc. which are setup the way that I want. I also have conditional formatting formulas set where I want. All appears to be working well.

What I want is that the result of the conditional format uses the style reference rather than the absolute reference by changing the format in the conditional dialogue box. Can Excel handle that? If so, how?

Any help on finding out why conditional formatting using vlookup and text doesn't work for me is greatly appreciated ...

Cells A2:A20 of my spreadsheet contains a data validation dropdown list from a named range of text values, i.e., when the user clicks A2, a dropdown appears and the user can select "Bob", "Bill", "Jim", "Sally" and "Pete".

I wish to use conditional formatting and vlookup to format the cells A2:A20 grey if "Bob" or "Jim" are selected, but leave the cell uncolored if either of the other three names are chosen. (This example is very simplistic - my actual spreadsheet has hundreds of unique dropdown text values, and vlookup would seem to make a lot of sense because a subset of these dropdown values should be colored grey if the user selects any one of them). When I try to do this using vlookup and conditional formatting, conditional formatting does not work for me.

The puzzling thing is that when I use numbers instead of names (say the numbers 1 and 2 of a list 1 to 5), the vlookup formula in the conditional formatting box works fine, i.e, something like =VLOOKUP(A2,$B$1:$B$5,1,FALSE) has no problems coloring cells, where A2 is the cell with the data validation dropdown list, and Cells B1 to B5 contain the listing of numbers that drive the lookup.

Thanks for any insights you can offer.

I'm trying to conditional format to highlight a date cell if its in the
current week.

I know whow to use WEEKNUM to check a week's number (1-53). I did check to
make sure the Analyst Pack was loaded.

The challenge I have is getting conditional formatting using formulas to
check the current cell

I'm trying the following formula: (In Conditional Formatting)
="WEEKNUM($C$4,1) = WEEKNUM($A$1,1)" with no results
C4 is the current cell, A1 is a refernce cell with TODAY().

How do I use conditional formatting using dates over 1 year old

Hello,

I am having many difficulties getting conditional formatting using the match function to work correctly in my excel document.

What I would like to accomplish is as follows in the 3Q09 tab. If a subdivision name is found in C-62 through C-70 and a match is found for the subdivision name in AB-24 to AB-66 I would like it so the AB-24 to AB-66 Subdivision name is bolded for every match. I removed the function formulas from the 3q09 tab I used to have since they didnt work and I do not want to confuse anyone.

This is correctly done on the "Working Correctly" Tab included in the same file, so please view this for clarification if you need it. Why it works on one tab and not the other simply baffles me.

Many Many Thanks

In need of some help with conditional formating using dates. I am working on completing a workbook that will use a green, yellow, red color format to highlight dates that are less than 9 month old, 10-12 months old, and greater than 12 months old, respectively. Any suggestions? Thanks.

Chris

I'm trying to conditional format to highlight a date cell if its in the
current week.

I know whow to use WEEKNUM to check a week's number (1-53). I did check to
make sure the Analyst Pack was loaded.

The challenge I have is getting conditional formatting using formulas to
check the current cell

I'm trying the following formula: (In Conditional Formatting)
="WEEKNUM($C$4,1) = WEEKNUM($A$1,1)" with no results
C4 is the current cell, A1 is a refernce cell with TODAY().

Hi Everyone,

I have a spreadsheet I am working on for a prospective retail store. I am currently comparing the potential ROI of Renting vs. Owning.

In the spreadsheet, I have created a drop down list that reads
Mortgage
Rent

I would like to have a custom format in a different cell that conditionally will read:

"Mortgage:" #.00 "Per Sq. Ft."

or

"Rent:" #.00 "Per Sq. Ft."

Can the conditional formatting "Use a Formula to determine which cells to format" make the adjustments (I have already tried several ways to write the formula in conditional formatting with no success).

Any help would be appreciated

Hi,

I'm having a hard time figuring out how to use the conditional formatting with a negative percentage. What i'm doing is taking two percentages and finding the differance (if there is one).

If there is a positive differance then I would like to color the cell green. (Example: anything greater than 0, like 5%)
If there is a negative differance I would like to color the cell yellow. (Example: anything less than 0, like -3.15%)
If there is a differance of -6.25% I would like to color the cell red. (Example: anything less than -6.25%, like -15%)

Everytime I try and do it the formula doesn't work. Any suggestions?

Thanks -

Hello,

I am trying to conditionally format a cell containing the %YTD Complete for an initiative to be Green, Yellow, or Red. That part is pretty easy using regular conditional formatting, but it needs to change based on where in the year today's date falls.

For example, if an initiative is 45% complete, but we're in the second month, that initiative is moving along very well (Green). If I have an initiative that is 45% complete, but we're in the 12th month, that intitiative is not moving along very well (Red).

If I made a reference table to explain what color percentages would be for that month, can I use that somehow in the conditional formatting?

Example:

Month...Red %.....Yellow %....Green %
1.........26%
2.........36%
3.........46%
(these percentage ranges would be decided by management for what they consider to be G/Y/R for each month (or quarter) and hard entered in)

I have J12-J62 containing the %YTD (calculated from other cells), and L1 containing today's date (using "=TODAY()" to calculate).

The main outcome of this request is to have a column with the YTD% which will automatically update to red, yellow, or green, based on how the percent changes throughout the year. The percent would change based on hard-entering a number reflecting completed projects.

I'm stuck as to if I need a macro, a script, or if it's just not possible to do.

Thanks for looking. If I've left any important information out, let me know and I will do my best to provide additional information.

I have a sheet that calculated percentages. I want to set up a conditional format color scale where 0% is green, 50% is yellow and 100% is red.

However, every time I use the three-color scale and percents, it applies to color scale based on the range of values present, not the full 0-100 range. So for example, if I have three cells with 5% 15% and 30%, then 5% will be green, 15% will be yellow and 25% will be red. If it were working in the way I would like, they would all be various shades of green and yellow green. I don't want to compare the values to each other, I want them compared to the range 1%-100%. Any ideas?

Also, can you do a color scale that changes the font color and not the cell color?

Thanks!

Hi. I'm using Excel 2010. My goal is to compare each cell on one sheet with the same cell on a second sheet (e.g, Sheet1 A1 to Sheet2 A1), and all cells much match exactly. The reasoning is that we have data that is transcribed from paper and entered on both sheets. One person enters it on Sheet1, and a second person enters it on Sheet2. This is done to ensure accuracy and integrity of that data conversion to electronic format. My hope is to find a way to set up conditional formatting so that IF a value on one sheet is different from that on the other, then the cells on both sheets will be highlighted yellow.

The formula I currently have setup in Sheet1: =NOT(EXACT(A1,'Sheet2'!A1)). The formula in Sheet 2 is similar, except it is pointing at Sheet1:=NOT(EXACT(A1,'Sheet1'!A1)). In the 'Applies to' box in the Conditional Formatting Rules Manager, I've got =$A:$IV.

This works beautifully as long as only data is entered. If by chance, however, columns or rows are deleted (which they oftentimes need to be during the reconciliation process), then the formulas and/or the regions they apply to are thrown completely off. Additionally, if/when data is copied from one location to another, it changes the 'Applies to' area.

Is there a way within the Conditional Formatting to apply the formula statically to the entire worksheet no matter what is copied/cut/inserted/pasted/deleted? I tried an Index formula but couldn't get it to work. I have posted this also to Excel Forum (http://www.excelforum.com/excel-gene...62#post2752662), and I did get one response; however, that response did not work either.

I would appreciate any advice. Thanks.

Frank

Hello people

I am just trying to format cells in A column depending on if cells in column B includes any date from month 07 and 08 for the same order number.

Example is attached. Sheet 1 is the raw data, Sheet 2 is what i want to succeed as a result using conditional formatting.

Can you lend a hand ?

Hi there,

I have a list of codes. Some of them may be duplicated. I want to use the conditional formatting and the IF statement to highlight in a particular colour any codes that have appear twice or more. I used to know how to do this but now I cannot get it right. Does anyone have any ideas.

Thanks,

Marcus.

Conditional Formatting in VBA
I need some help with a VBA code. I need to create a fourth format in VBA to format the cells based on a fourth condition

Currently I have a couple of cell ranges that are using the three conditional formats. The conditions are as follows:
1) If the task is current - no conditional format
2) If the task is complete, the cell is green
3) If it is getting close the cell is yellow
4) If it is late it is red.

5) Need help -- Need the font color to be white, when cell equals "#NA"

In order for the chart to work, the tasks that are not assigned need to have #NA and not "". I would like to the #NA to appear blank by using the white text. need to use a fourth format that will turn the font color of a cell white (make it appear to be null) when it shows the #NA result. The only way to do this is through VDA code.

Can someone help me put together a code that will add the fourth condition to the ranges below?

The ranges are as follows:

D55:N55
D66:N66
D71:H71
J71:N71
D81:N81

Hello,

I've been working away at this problem for a few hours this afternoon to no avail. I am trying to make an excel sheet to help calculate student grades, with a few specific requirements.

I have a column of numbers from G4 to G19. 5 numbers in Total top 4 numbers are 3 cells merged together. I am able to use conditional formating equal to using =max($G$4:$G$19) then format green, this fills the cell with the highest number in that cell green, which is fine.

Now within that there are 3 numbers in a column to the right that are associated with that specific highest number. For example, lets say merged cell G10 (composed of G10 to G12) has 3 numbers to the right of it in I10, I11, I12, I want to also fill the color in for the highest of those 3 numbers, but only if G10 is the highest number in its column first.

I will attach an example, currently level 2 which has 7 results is highlighted as meeting the conditional format for highest number in column G, what I want to now be highlighted as a result of that response is I11 which has the highest number out of I10 to I12.

There are levels 4, 3, 2, 1, R, M within those there are 3 sub levels.

Essentially they have a mark pool in yellow. How I am to calculate marks is count all of the 2's for example including, 2+, 2, 2- if there are more 2's than other numbers then they get a 2 grade, however, I now need to know which they got more of within the 2's be it 2+, 2, or 2-. I want to highlight which level they got more of (in this case they had 7 2's) but also want to highlight within the 2's which they got more of (in this case 2) however, I need that result (5 in this case) to be highlighted also.

Your help is appreciated

Tyler

Hi

I want to use conditional formating on a whole line. I want to compare two cells in that line. If the cells are not equal I want the whole line to highlight.
Please see example below

I want to compare cells C and E and cells D and F. If either C and E or D and F do not equal each other I want the whole line to highlight not just the cell. As shown below Line two should be highlite because C dose not equal E. I know how to get a single cell to work but not the whole line.

see attatched

thanks

I have an excel spreadsheet that contains blocks of info in a 3x4 block. The block I need to use as the conditional format is the lower left cell. However, I do not know how to conditionally format the other cells based on if this cell is colored. Here is a visual:

A B C
1
2
3
4 Data

So in the conditional format I want A4='Data' color blue. I then want A1:C4 to also be blue if A4='Data'. Is Offset the best way to do this, or am I over thinking the problem? Also, I want the blocks to retain their colors when the scheduler moves them to a different sheet.

Hello,

I am trying to create a conditional format using VBA and apply that format to several rows in 2 columns. When I walk through the code the formula string contains the correct cell reference, however when the macro completes and I review the conditional format for the cells in the sheet the cell reference starts 9 rows and 1 column past what I want. I want the formula in cell A10 to be "=LEN(A10)>0" but when its executed I get "=LEN(A19)>0"
Below is the code I've created. (Note: wrkSht is a worksheet variable initialized to the correct sheet. This code is in a method inside of a class module)


	VB:
	
firstRow = 10 
firstCol = 1 
lastRow = 500 
lastCol = 3 
Set rng = wrkSht.Range(wrkSht.Cells(firstRow, firstCol), wrkSht.Cells(lastRow, lastCol)) 
rng.FormatConditions.Delete 
 
strFormula = "=LEN(A10)>0" 
rng.FormatConditions.Add Type:=xlExpression, Formula1:=strFormula 
rng.FormatConditions(1).Interior.ColorIndex = 24 
 
Set rng = Nothing 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines



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