Excel - Applying Color To Cells?
Sep 19, 2013
I am using two separate workbooks. I am tranferring dates into a new spreadsheet to track projects dates for milestones.
The formula I am using is this: =IF('[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$CQ$4="", VLOOKUP(W54, '[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$1:$1048576, 94, FALSE), VLOOKUP(W54, '[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$1:$1048576, 95, FALSE)).
I would like the cell to turn green if the last part of the formula is true and stay clear if the first part of the formula is true.
I also need to add color beyond just that. I was attempting to apply conditional formatting but am a bit stumped. I want the green to be maintained regardless of other formatting. I would also like to apply to cells that are not color coded green:
Red - if the date is overdue
Yellow - if the date is within 7 days
Otherwise, leave the cell color as clear
Is this possible with conditional formatting? I am not at all proficient in VBA...
View 9 Replies
ADVERTISEMENT
Feb 7, 2013
I am using Excel 2010 and basically i am trying to fill a range of cell with a green color if any value was enter in a specific cells. Example: I would like to fill range: A10:c13 with a green color (regardless of the cells content in this range) if a value was entered in cell C10 or C11 or C12 or C13.
I've tried conditional formatting but unfortunately I'll have to apply formatting for every cell and for a range of over hundred cells is not efficient.
View 7 Replies
View Related
Sep 21, 2013
I want to have a condition which will check if the value in cell is present or not and based on that i have to display result in a cell.
Eg: In Sheet1, in column D i have 4 values (Simple, Complex, Medium Complex, Very Complex)
In Sheet 4, we have numeric values to Simple, complex...Like below
Complexity Estimation
Simple 2hrs
Complex 4hrs
Medium Complex 2.5hrs
Very Complex 6hrs
In Sheet5, we have values defined in % like below:
Requirement Analysis
15%
Design
20%
Coding
20%
System Testing
15%
[code]....
In column G of Sheet1, we have to write a function or formula which we do below calculation.
IF (Sheet1!D3="Simple") then Sheet1!G3=Sheet5!B2(ie.15%) of Sheet4
Similarly i will have Sheet1!D3 values as Complex,Medium Complex,Very complex
View 2 Replies
View Related
Dec 1, 2011
Is there a way of preventing Exel from Applying table styles, when converting a range to a table. and leave the formatting of the table intact?
View 1 Replies
View Related
Jul 23, 2013
I have a number of different modules. Each module does something similar, but different to a specific workbook. Instead of running the macro(s) individually is it possible to have a parent-class that calls on each module, and further to have each module *know* which worksheet it should apply to?
Example below:
Code:
Sub Cost_Center_Information_File()
'What it does: applied to a worksheet (there are about 15 worksheets in the workbook) it
'will do some formatting manipulations.
[Code].....
View 4 Replies
View Related
Jan 10, 2014
I want to color cells if it is texted as ULF by green, if it is texted as OS by red in the all row down, it easy but I can't.
View 2 Replies
View Related
Jan 9, 2014
I am trying to update a project tracking sheet at work and I am having trouble. I have a list of construction projects (approx 130) along with details on the project, one detail being % completed. I am trying to create a summary of the projects at the bottom of the page, showing the total value of all projects, according to the % completed.
<10% is ANTICIPATED
= 10% is AWARDED
>10% <90% is IN PROGRESS
>90% is COMPLETED
[code]....
View 8 Replies
View Related
Mar 3, 2008
I use the If - Then statement in VBA to determine a condition which works perfectly fine for the particular cell I reference to, but i need it to apply to all the cells i.e. cells A1:A10?
Sub MACRO1()
If Range("A1") = "PAYE" Then
Range("B1").Formula = "=C2 * .128"
End If
If Range("A1") = "LTD" Then
Range("B1").Formula = "=(C2 - 100)* .128"
End If
End Sub
Sam
View 9 Replies
View Related
Aug 16, 2012
Excel 2010, Windows 7
I have a whole ton of *.dbf files (98 to be exact), that are from ArcGIS shapefiles. These date1_date2.dbf files have point names, and values, as shown below.
NAMERASTERVALU
Point 1-9999.00000000
Point 2-9999.00000000
Point 3-9999.00000000
Point 4-4.93072701
Point 5-8.90071201
I'm trying to figure out how to transpose each of the dbf files and save them as a text file (tab deliminated). My original plan involved saving them all as text files, then using a convoluted Unix script (I'm what you call a "dirty programmer") to transpose them and then combine them all into a single file. Since there are so many files (and I plan to do this in the future as well), I thought if I could at least automate the text file creation, I'd be ahead of the game. Even better would be a macro that will transpose the data for EACH file, then create a 99 (98 files + header row) row, 5 column text file. Creating the text files (1 text file for each dbf file) automatically .
All the *.dbf files will have exactly the same # rows/columns (2 columns, 6 rows)
This is what I've tried so far.
1)open one of the dbf files in Excel
2)record my marco (using the Personal Macro Workbook option - so it will be available in any workbook)
2a) transpose data (and paste it right below the original data so that the new data is in rows 7 and 8 of the same file)
2b) save as text file (tab deliminated)
2c) stop recording, and end up with this:
NAMERASTERVALU
Point 1-9999.00000000
Point 2-9999.00000000
Point 3-9999.00000000
Point 4-4.93072701
Point 5-8.90071201
NAMEPoint 1Point 2Point 3Point 4Point 5
RASTERVALU-9999.00000000-9999.00000000-9999.00000000-4.93072701-8.90071201
So now I want to apply the macro I just created to all open workbooks (after I bring in all the bdf files), but the macro isn't listed when I click the "view macros" button. All I want to do is apply the steps I recorded in the macro to all open workbooks.
View 6 Replies
View Related
May 9, 2013
Excel 2003 user.
I have a work sheet with numerous cells coded one of two ways. Either a color of red or a color of green. Is there anyway to count how many times green cells show up? etc..?
I know I'd need to know the exact color but it would save a lot of time as I need to find out how many cells are of each color.
View 3 Replies
View Related
Oct 3, 2011
I am wondering how can I count different color condition color cells?
I am currently using this UDF,
Code:
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
[Code]...
However, this code count the entire conditioned color cells that I have. What I want is for the code to count two different colors within the same column range (like B1:B10). For example, I have 3 red color cells in range B1:B10 and 7 green color cells in range B1:B10. So, I want it to count 3 and 7 seperately rather than giving me the total of 10.
Important Note: As it is a conditioned coloring, there is no fixed coloring as to which cell will have the red or green since it is based on conditions.
One last time, I am using Excel 2007.
View 1 Replies
View Related
Jun 8, 2014
I want to apply a simple formula to a number of cells in an existing workbook that already contain values E.g.
My column contains the values
5200
1600
4376
in separate cells
I want to divide each value by 1.2 - without having to change every individual cell - can it be done?
View 2 Replies
View Related
Dec 28, 2007
Is it possible to apply Filter utility on Column Cells that are 'coloured'?
View 9 Replies
View Related
Dec 17, 2008
I have thousands of path names in a spreadsheet that were converted to hyperlinks, but my client wants to see only the filename in the hyperlink, not the whole path.
I created the hyperlinks by applying the following to the column containing the file paths: ....
View 9 Replies
View Related
May 16, 2014
I am trying to use the IF formula or a similar VBA/Macro to color certain cells. We deal with 16 different sand types that come in on rail cars. I want to put in the sand type in a cell; EX: C11 has text "30/50BH" if this statement is true to color cells A11-D11 yellow. If it is not a true statement to check for the next sand type, "20/40BH" and go on from there. If I can get an example of what to do I can build it for the 16 sands we have.
The formula I have in mind would be something like
=IF("30/50BH",[colorA11:D11,Yellow],[IF("20/40BH",[colorA11:D11, Purple], .......
I'm sure there is a less brute force method of doing this, but my knowledge of programming and excel is limited. I am using the 2010 version of Excel.
View 14 Replies
View Related
Feb 16, 2014
I want to color the cells for some specific texts in RED, and some in BLUE and some in GREEN.
For Eg: I want to color the cells in RED, which contains below specific texts in a sheet.
UNIX
LINUX
MS OFFICE
POWER POINT....like this for nearly 100 specific texts.
and I want to color the cells in BLUE, which contains below texts in a sheet.
JAVA
.NET
SQL
MSDOS....like this for nearly 100 more specific texts.
If i create a rule in conditional formatting for each text,I have to create nearly 400+ rules for my task now, which is not possible.
View 5 Replies
View Related
Aug 17, 2014
Supposing you would like to find the median of numbers in column D, but only for rows that have the word "Jones" in column A? Is there a way to do this? Obviously I know I could do a sort and simply specify the range myself, but we're dealing with nearly 2000 rows and a LOT of different values in column A
It occurs to me that it would be even better if I could ask excel, in effect, to identify every separate text string in column a and then find the median of the corresponding numbers in column D. In other words I wouldn't have to type any strings from column A at all.
View 2 Replies
View Related
Apr 25, 2012
If you have a range of cells with values in them (Q2:AZ2), you can use conditional formatting to identify the top x% of the group and it would essentially treat each cell in comparison to the group. I need to identify if a cell in the group is greater than 3*Stdev(Q2:AZ2). Do I have to create conditional formatting for each individual cell or is there a way to apply a function to the group that would recognize each cell the same way that the canned conditional formatting rules work?
View 1 Replies
View Related
Dec 26, 2009
how can i do the following using VBA
making each cell in column A added to each cell in column B and the result will be in the column C
for example
c1=A1+B1
c2=A2+B2
c3=A3+B3
... etc
i know i can do that simply without vba code but I just want to use this method to implement more complex formulas .
View 9 Replies
View Related
Mar 20, 2007
how do I apply a formula I entered into one cell to all the cells in that column, without going through the tedious process of typing the formula in again in each cell ? I've done it before but don't remember how.
View 2 Replies
View Related
Jul 10, 2012
I have an Excel 2007 Workbook which is refreshed by a Web Query. I have dynamic named range defined as "Manager" which I've confirmed is correctly identified. This range includes only one column and is formatted as text.
When I try to reference this range in my conditional formatting "refers to" formula, all cells are recognized as blank even though the range clearly contains many cells that are not. For example, there are 90 records containing the initials "PD". If I use the formula =Manager="PD" in my conditional formatting, nothing changes. If I change it to =Manager="" then ALL records are formatted, including those that are not blank.
Stranger still, if I enter the formula =COUNTIF(Manager,"PD") into and empty cell in my worksheet those 90 records are counted correctly. Which leads me to believe it isn't about the data. Conversely, =COUNTIF(Manager,"") returns the correct count of only cells that are, in fact, blank.
I've tried using the OFFSET formula defining my range in place of the name itself for my conditional format formula to no avail.
View 2 Replies
View Related
Apr 23, 2014
I was messing around with some Macros, as an issue came up where clients were pasting over Data Validated cells (thus removing the validation). Somehow though, the feature where you can select a cell, then pull the active cell down through the column (and then can copy, fill series, etc)has become disabled. I deleted all the macros, and turned them off in the Trust Center, but when I click on a cell the box and plus sign still won't display for me to pull the cell down the column.
View 2 Replies
View Related
Jul 18, 2014
So I would like to automate the following, as doing it by hand takes a lot of time. I have searched the whole internet and finished empty handed.
Start from cell E10
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Apply double entry conditional formatting
Move 3 cells down
Repeat above until cell E5000
View 4 Replies
View Related
Feb 4, 2014
I'm trying to find ways of applying a formula in condition formatting to find entries which don't follow a certain format.
to be clear, this is for a reg plate of a car. There are two styles of formats. The X's represent A-Z and # represents numerical values
1. XX##XXX
2. XX####
find a way of applying two test cases (non-simultaneously) so that cells highlight if they don't follow either of the above conditions?
View 1 Replies
View Related
Dec 2, 2013
I am using Windows 7 and Excel 2010.
Is there a way to create a macro to color code a cell based on the value in a cell, and then look up a value in a table, then color code it based on where it fits into the table?
I have a table of values for about 30 projects. In column g - there is a CPI value (see bold column)
Example: Project ID
Name
Program
PMT
SI ID
AC
Milestone
TCP Level
[Code] ......
Here is the table:
I have to color code a cell, base on the CPI and how it fits into the table below. So if the current Milestone is M2 or M3 and the CPI calculated is .14 the cell would be colored RED, if the CPI number is 2.01 for M2-M3 I would want cell to be colored Turquiose. If we were at Milestone M6 and the CPI was 2.01, it would be colored blue. If the CPI was .75 at Milestone M5, it would be colored Green
LEGEND
Earned Value Limits
Milestone
RED
Yellow
Green
Turquoise
Blue
M2-M3
2.15
M4-M6
1.66
M7-M11
1.26
View 2 Replies
View Related
Aug 8, 2012
I am trying to use VBA in Excel 2007 to change bar chart series colors. I have found a few posts that link it to a cell background, but I'm struggling to find one that does the font color.
It would be great if I could change the bar chart series to match the color of the text in the A column, so that if I highlighted the value in A1 and changed the text color to orange for whatever reason, the chart updates the value of 1.2 to an orange bar (see below).
T. A1 B1
Sample 1 1.2
Sample 2 2.1
Sample 3 1.7
Sample 4 5.6
View 3 Replies
View Related
Aug 31, 2012
I have a range of cells that change colors with conditional formats based on the cell value from high to low. I would like to link the cell color to an object such as a circle or rectangle. When the cell value changes along with the conditional format, the color of the object will also change.
View 3 Replies
View Related
Oct 27, 2009
I have attached the relevant spreadsheet for which I need to alter the color of the columns based on Site number ( Sheet 1). % Mortality will be represented in the Y-Axis, and the Site numbers would be on the X-Axis. All columns (% Mortality) except one will be of the same color, and the one of a different color will indicate a specific site. As an example, site 86 is colored differently. The way I require the chart to look is shown on Sheet 1.
After reading through some great posts on Ozgrid, I managed to do this using conditional formatting (Sheet 2), but that sort of falls short because I am required to add a data table to the chart, and the parameter that is indicated by the column bars happens to appear twice in the data table.
I was wondering if this can be automated maybe using VBA, but with the possibility of simply matching the color of columns with the font color of respective entry in the data series.
View 9 Replies
View Related
Nov 18, 2007
I am looking for a very simple script that will achieve the following:
On the clicking of a button, Select and shade in a cell yellow, delete the yellow shading of the previous cell. The shading & selection should move up a column of cells, 1 at a time, in the following order:
From B10 to B9, then B9 to B8, B8 to B7 etc until the selection and shading is at B2. Once it is at B2 subsequent clicks will simply keep it at B2 (the top). Thus after 8 clicks the shading & selection should travel from B10 to B2, with only 1 cell being shaded yellow and selected at any one time.
View 5 Replies
View Related
Jun 18, 2008
I have a spreadsheet that i download from the net daily, which is seperated into columns of information.
I want to be able to look down a column and mark a cell in a seperate column if the cell font text is red.
For example looking down column A ... if the font text of a1 is red then mark the cell background colour of T1 red - if a2 text colour is red then mark the cell T2 red .... etc etc.
If the font colour in a1 or a2 ... etc etc is any other colour then do nothing.
I have 5 columns I wish to look down and mark in 5 seperate columns - I have tried to do this by conditional formating but don't know the fomula for checking font colour.
View 9 Replies
View Related
