In column "L" there is the possibility to have one of the following characters entered as a key for that specific row;
G
X
B
G1
G2
S
Y
H
SB1
SB2
They all have a unique color assigned to them. There are too many for conditional formatting, so I think the way to resolve this is to use VBA. Can I ask for some assistance to get me started?
example.png
I am sure this is simple and easy to do, but i am having difficulties achieving this.
I want the cell to fill as it gets closer to 100%, I am sure its conditional formatting but I cant seem to get it to work.
I am creating a spreadsheet questionnaire and I have a cell where a selection is made and based on that selection, I want it to populate another cell with a different list of information. In simple form:
Drop down picks a name: Bob
Quote number (when bob is selected): 34xxx
I've created a drop down box which displays a list of headings for columns. The user enters the total for that heading in that column and then a total column sums all the entries for that row. However, I wanted to simplify it so that the user just enters the total and then selects the column heading from a drop down list and then the value entered in the total column is automatically entered into the column corresponding to that heading in the drop down box as well. I've attached a jpg which should give you an idea of what I'm talking about.
The values in column E are the ones the user enters, I then want these values to be put into the corresponding row based on what they select in the drop down box in column F
I'm trying to record a macro that is using vlookups to get data from another data sheet, this data sheet will update each month - the old data moving back a column (so the new data is filled into the same column each time).
However, on the sheet where i am recording the macro, i need the data to fill in the next column each time.
I have recorded the below:
Sub Colourants()
'
' Colourants Macro
'
'
ActiveWindow.SmallScroll Down:=-30
Range("AK3").Select
[Code] .......
How do I get it so it doesn't record in cell 'AK' all the time but the next empty column each time it's run (however, it will always run from row 3).
I'm writing some VBA code for Excel. I need to change the color of a cell based upon the data in another cell. This selects sheet "Kane", and searches for the word "Expenses" in a range in column D. Using For-Each, I need the IF statement that will change the cell color in the same row, but in column P.
View 4 Replies View Relatedcode which highlights a cell depending on the criteria of another cell.
Example.
In B1 I have the formula =NOW() (we all now what this means).
In B2 I have a completion date.
I would like A2 to fill with a specified colour.
Can this be done so that it works down the whole sheet, if the date is in B5 then only A5 will highlight so basically only the A cell on the same row will change depending on the date in B on the corresponding row?
VBA code to do the following.
In F147 will go a value
Then if the value of F147 is greater than 20 the color of D144 would turn green.
I am trying to a macro that allows me to change the backgroud color and font color depending of the value of the cell.
For example is cell A1 is having the value between 1 to 3 the background color of the cell will the light turqoise, if the value is between 4 to 20 the cell background color will be green and so on.
I have based on certain posted example and adapt to my code but somehow it is not working.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Select Case Target.Value
Case Is = 1, 2, 3
Target.Interior.ColorIndex = 34
Case Is = 4, 5, 10, 20
Target.Interior.ColorIndex = 43
Case Is = 30, 40, 50
I would like to have a vba code that changes the cell color so that if there is number 1 (just number 1) in some cell the background of the cell changes in to shade of grey and cell that has number 2 changes into darker shade of grey and so on. I have numbers 1-10 in random order in my sheet. I mean there is many cells that has the same number and the sheet is quite large so vba code would be ideal choise to do it quickly.
View 9 Replies View RelatedIīm trying to do a macro that changes the color of all fonts in a row if the cell in the column "R" has the date lower than a specific one, something like this:
Dim Data2 As Date
Data2 = Sheets("Sheet1").Range("today")
Range("R12").Select
Do While ActiveCell <> ""
If ActiveCell < Data2 Then
ActiveCell.EntireRow.Select
Selection.Font.ColorIndex = 3
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
But it just donīt work. The macro does that in the first row, than it stops. Do you have another code for this, or another way to do?
How to "hard code" a cell's fill color? I want to assign a cell's fill color to yellow. While setting the fill color is easily done, my problem is that as soon as I paste data into that cell, it looses its fill color. Is there a way to tell the cell to never change the fill color?
View 1 Replies View RelatedVBA code in worksheet won't revert a changed cells color back to 0(white) after removing the comment.
Observations: code does set the desired cell colors to Yellow after inserting a comment, however removing said comment, and re-activating the worksheet does NOT reset the color after deactivating/re-activating the worksheet.
[Code] ......
I have cells in range L12:BN1000 with formulas that will output a 1 or a 2.
If the output is 1, I would like the cell to color yellow
if the output is 2, I would like the cell to color orange
the reason I dont want to use conditional formatting is because the use needs to be able to copy and insert rows and by doing so the use would need to manually add the new cells into the conditional format range. I would like to come up with a macro that applies this condition to a large range.
also, were do I add this macro? under sheet1? this workbook? or as a module?
I have an excel spreadsheet where column "D" will have one of fifteen different names inserted. According to which name is present, the cell needs to be a specific color. Any ideas on what would be the best code to use to read the entire column and format the cells that have names?
View 9 Replies View RelatedI have 16 columns (B:Q)
Row 1 contains either a number, the letter x, or the letter a.
Cells in Range("B2:Q628) contains either the letter x or is blank
If a cell in row 1 contains an x, then all cells in that column with an x has an Interior.ColorIndex of 45
If a cell in row 1 contains an a, then all cells in that column with a blank has an Interior.ColorIndex of 45
If a cell in row 1 contains has a number, then nothing
I have a table with rows and columns, to record status of various activities. One of these columns is to record when an update to a cell in the row has been made. I would like a macro that would, when the data in a cell has been changed, to highlight that cell in a different color and enter the date the change has been made, in the update column "Last Updated" for that activity. This way I can see what cells have been changed and when.
View 3 Replies View RelatedIf I have a cell containing a formula say c1= sum(a1..a10) and the output appears in a50. how do I colour code it so I know the formula in c1 is linked to the output in a50?
View 5 Replies View RelatedI have about 30 spreadsheets that I need to sort data. I'm using a Excel: Mac 2011.
Each sheet has about 100 rows and 80 columns.
I need to extract the rows that have a colored cell somewhere in the row. There are three types of colored cells: black, green, and red. The green and red have X typed in them too. The black cells have D typed in them.
Every row in the sheet has at least one cell with an X in it (colored or not).
I just need the rows with a colored cell to be extracted or filtered from all the rows without colored cells.
I have the code below set to change the background color based on certain conditions. What I need to do is change it so that if none of the conditions are met, it does not change the color at all. As it is written now, if anything is entered in a cell that has a background already set, but does not meet these conditions, the cell turns white.
View 4 Replies View RelatedI have a worksheet that when a row changes based on the value of column B, I want to remove all of the formulas found in the row but keep the existing values, and then change the color of the row.
In the sample file attached, when the value is "Closed", that row will keep the existing values and then it gets grayed out. Rows that are still marked "Open" need to retain the formulas in case other information changes.
I have tried copy/paste special using autofiltering but that doesn't work because of the hidden lines. This file changes on a daily basis and I need a quick way to update the file.
SampleFile.xlsx
Summary of performance of various products against target is as follows,
Product vs Target
Color Code
Result
CH4OH
Green
1.0
[Code] ........
I need the final result automated as follows,
If 2 green of the 4 products, then final result Gree
If 2 Amber of the 4 products, then final result amber
If 2 Red of the 4 products, final result Red
Is there a way to automate this?
I have a spreadsheet that will contain about 5-15 rows with a letter "S" in the column. If this letter S appears in the column, I need its entire row to change font color to RED and then change that row's value in column L to a negative number. is there any easy way to do this?
View 14 Replies View RelatedI 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
Looking forward VBA coding for:
If input is entered into column C (range C2:C100), then row cells for columns D, E ,F, H or I must NOT be blank upon save.
Could be that one or more of these row cells are left blank by mistake.
An error msg pops up upon attempting to save, stops the save and colors each cell yellow that needs info entered into.
Using Excel 2010.
I can't seem to find the answer to this. In VBA, how do you Find a cell or row based on multiple criteria? All I need is to find it's location. If it helps, here's an example:
Sheet 1 contains source data:
Date Name Time Complete
Sheet 2 contains an interactive worksheet using this data. On Sheet 1 there's an entry: 9/12/2006 George 03:30 PM
Sheet 2 displays those records with adjacent cells for entering data. When data is entered, I need the system to go back to Sheet 1, find the entry that matches this one, and put an "x" in the Complete column.
Does anyone have a VB macro that will copy information from a cell (this will be variable based on criteria) and paste it into the middle of another cell that contains HTML code?
The criteria for the copy/insert/paste is that the information in the cell that is to be copy/inserted must match up with the correct number (part number).
I hope this makes sense. I have attached an example spreadsheet. (Pictures are worth 1000 words. ) Hope someone can help. I checked this site and couldn't find anything that was solved that matched the specifics.
I would like to add a date to one cell (say A6) and have this do two things:
#1) this would add "Closed" to a given cell such a A5.
#2) and this would add a color to a group of cells like A1 through A8.
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
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