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......
The code below will locate and select cells containing Kenny. What I want to do is create a little search cells(C5) on my spreadsheet and have the code reference whatever is typed into the search cell. I'm sure it's a simple adjustment. I've tried changing the What:="Kenny" to What:="=C5" & What:=C5 all without success.
Sub find() Cells.find(What:="Kenny", After:=ActiveCell, LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ True).Activate End Sub
I have attached two files, one the sample txt file imported to excel but the top area is highlighed with some notes and section break. RED deleted don't need amd un highlighted is actual data. The other file is what I would like to see the data look like after parse.
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.
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 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 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
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
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.
I 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
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.
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.
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 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 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 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.
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?
I 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
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
Is it possible to use different text colours as part of the [Value_If_True] or [Value_If_False] when using an IF formula? For example =IF(AS3<34,"ok","not ok") In the above formula I would like the “ok” value to be in Blue and the “not ok” value in Red.
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?
VBA 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.
