I have a code that updates data from one file to another.....esentially, i run this code daily and it updates to a compiled daily log of all of the previous days data.
Currently, i am manually coloring the cells to visually assist in grouping what data has been uploaded for each day.
Sub UpdatePOQntyReducDB()
Dim lookupfilename As String
I am trying to create a model that has a mixture of conditional formatting and data validation formulas within it, but have run into a problem in one area. Essentially, cell E10 is a Validation which pulls a drop down list of names. One of the options in this list is "Other".
I want to create a VBA code to make sure that cell E12 will get rid of any input, turn grey (color index = 15), and potentially lock (if possible!) if the "Other" is chosen by the user in cell E10. If any other value is chosen for cell E10, Cell E12 needs to remain unlocked and white (color index=2).
I must highlight cells that appear more than once.
What I mean is that if an item in a cell appears more than once I highlight it in blue, if the same happens twice I highlight that cell red etc and so one. does anyone know how to do this in excel.
Well I am back to the colour coding of protected cells
I am using excell 2003
Many cells in the worksheet are locked
My problem is that when I am debugging I forget to turn sheet protection on
when I am finished And then users can overtype the cell formulae
How can I set excel up so that when the sheet is protected all the Locked cells have a green background , but when the protection on the worksheet is turned off
then the locked cells must be a red
I am looking for code that would delete all columns in a worksheet that do not contain a fill color.
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 this code:
Code:
If .Range("K" & t) = "AUTO" Then
.Range("K" & t) = Format(Application.VLookup(.Range("A" & t), rcore, 54, False), "h:mmA/P")
With .Range("K" & t)[code]....
It is intended to change the font colour to white when the cell = "AUTO". It does, but it also changes the contents of the cell (like NA, and NR) to white when they aren't equal to AUTO.
I have a spreadsheet with a row for a company name, their offer price underneath that, and a 1,2, or 3 underneath that. In a seperate tab I have the offer prices in order of rank with the company name next to the corresponding price using the Index function. I also need to be able to color code the background of the offer price based upon the 1,2 or 3 associated with each name. I am new to the Index stuff so I need some help putting this one together.
View 3 Replies View RelatedI've created a Gantt chart using the tip #58. Now how can I count all the cells that are colored?
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
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.
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.
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.
I use the code below to automatically set a black dot in cell
A11. I would like to add code which would color that dot
the same color as a manually placed checkmark in column C11 which
is symbol é.
In other words, in addition to the code printed below, I would
like to color the dot the same color as the color of the arrow
symbol é in column C11.
Is that possible? Thanks
=IF(AND(B11="ü",D11="X"),CHAR(108)," ")
I was wondering if there's a way to make the numbers in a column over 50 (as an example) green and numbers less than 50 red. (The numbers in this column are the sums of other cells, but I guess that doesn't really matter.)
This way when there's a lot of things going on you can see what's what without filtering or rearranging.
Is there a way to Print VBA code in color, like how it appears on the screen?
View 1 Replies View RelatedIs there VBA color to change ".Fill.ForeColor.SchemeColor =" into NO FILL? In other words, keeping the existing Foreground color prior to the macro being activated.
I'm in a chart.
part of my code looks like this. Instead of it being if c.interior.colorindex i want it to be if top border color = 4.
How do i syntax this?
For Each c In Worksheets("Sheet1").Range("B7:DE7").Cells
If c.Interior.ColorIndex = 4 Then Range("DH7").Value = Range("DH7").Value + c.Value
Also, is there a way to specify Range("DH") and make the row be the current row, not hard coded to 7
I am using the following code to change colors in set of autoshapes (Thanks Colin_L and Norie) and it works fine for the first autoshape but has it has no effect on the subsequent autoshapes. I plan on using the code for about 200 autoshapes, I thought I could just copy the first code and keep adding it on for each autoshape... I am guessing that the code is stopping after the first set and not continuing?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$46" Then
'Change autoshape color to red depending upon cell value, or blank of no value is entered.
With ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor
If Target.Value = "" Then
.SchemeColor = 1
ElseIf Target.Value >= 422 Then
.SchemeColor = 50
ElseIf Target.Value >= 1 Then
.SchemeColor = 10
Else
'it must be less than 1
End If
End With
End If......
Is it possible to have a code that says if I have a range of cells....say a1:d1 with d1 having a date in it....have that code say that if that date in d1 is greater than 30 days from today, make that whole range of cells (a1:d1) change color like to red.
View 9 Replies View RelatedI am looking to color code items in a list box called lbActiveItemList, is this possible? If so I would like it to color code based on the value in the 3rd column as follows:
If the value = 'Receive' Color code the item line as Black
If the value = 'Return' Color code the item line as Blue
If the value = 'Relocate' Color code the item line as Green
If the value = 'Lost' or 'Damaged' color code the item line as Red
Is there some code to change the fill color of all the combo boxes I have on a spreadsheet.
View 2 Replies View RelatedVBA 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 am wondering if there is any good way of coloring different sections of the source code in excel to show what different programmers have added?
I am trying to color code mistakes I find when running formulas in a macro. Right now I have it set up to color code mistakes red using the following code after the formula has been inserted in column E.
Range("E2:E75").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""Check"""
Selection.FormatConditions(1).Interior.ColorIndex = 3
My issue is, some of my checks have more than one variable as a wrong answer. For example, one check may include Check and Bad as possible outcomes of the formula in the cell.
the code required to allow two different variables to cause the cells to change color.
I tried a second set of the code for the same column and changed the formulas word to Bad but it did not highlight the cells that came out with Bad as the outcome of the formula.
I have values that I want to color code in my table. Originally I was tagging them as numbers, but instead I need them as colors.
Function conversion(pVal As Double)
If pVal > 0.05 Then
conversion = "1"
ElseIf pVal > 0.001 Then
conversion = "2"
ElseIf pVal > 0.0001 Then
conversion = "3"
ElseIf pVal > 0.00001 Then
conversion = "4"
End If
End Function
Now I want instead of conversion = "1" etc to be
Function conversion(pVal As Double)
If pVal > 0.05 Then
(make white)
ElseIf pVal > 0.001 Then
(make brighter blue)
ElseIf pVal > 0.0001 Then
(make green)
ElseIf pVal > 0.00001 Then
(make navy blue)
End If
End Function
I want the text and cell the same color (number and cell so the number disappears so that I just have a colored cell but the value is still there if I want to look at it).
I am trying to color code the cubes in my chart. I'd like to be able to be able to specify a specific color per cube. What code what I write for the Sheet to do this?
View 5 Replies View RelatedI searched through the files and found a macro that works for me to color code my spreadsheets in Excle. Hwoever. How can I set it to stop when Cloumn G = the word "Created"?
Sub prettyPattern2()
Dim lRow As Range
For Each lRow In Range("A6:K500").Rows
Select Case lRow.Row Mod 2
Case Is = 1: lRow.Interior.ColorIndex = 34
Case Else: lRow.Interior.ColorIndex = 35
End Select
Next lRow
End Sub