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

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

- Conditional Formatting using Percentage Variance
- Conditional Formatting Using Percentages
- Conditional Formatting: "Use a Formula to determine which cells to format," ?
- Conditional Formatting using 2 columns with dates and a 30 day interval
- Conditional Formatting using a macro
- Conditional Formatting Using Styles
- Conditional Formatting using VLookup and text
- Conditional Formatting using Formulas and WEEKNUM
- Conditional formatting using dates
- Conditional Formating using Match Function
- Conditional Formating using dates
- Conditional Formatting using Formulas and WEEKNUM
- Conditional Formatting, using custom formats
- Conditional formatting negative percentage
- Conditional Formatting - YTD Percentage Complete Compared to Current Date
- Conditional formatting with percentage color scale
- Conditional Formatting using a formula referring to another sheet
- Conditional Formatting using IF
- Conditional formatting using the IF statment
- Create additional Conditional Formats using VBA
- Conditional formatting using max statements
- Conditional format using formulas
- Conditional Formatting using Offset
- Apply conditional formatting to a specific range of cells using VBA

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.

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!

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

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:Thanks againCondForm() Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G$1$F2" Selection.FormatConditions(2).Interior.ColorIndex = 37 End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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?

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.

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().

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

Chris

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().

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

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 -

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.

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!

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

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 ?

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.

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

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

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

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.

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