VBA Code - Color Changing Cells

Apr 29, 2009

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

View 9 Replies


ADVERTISEMENT

Code Changing Color Property Of Wrong Cells?

Apr 14, 2013

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.

View 2 Replies View Related

Changing Color Of All Comboboxes With VB Code

Jan 28, 2012

Is there some code to change the fill color of all the combo boxes I have on a spreadsheet.

View 2 Replies View Related

Difficulty Writing Code For Changing Font Color If Cell Contains Specific Text

Aug 11, 2009

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 Related

Changing Fill Color In Cells

Jun 23, 2014

I have no knowledge of VB programming and the conditional formatting seems limited. I want to change to the color of cells to yellow if the cell value is greater than 80 but less than 99, and if it is 100 or greater to fill red. Is this possible and if so can it be applied to a range of cells?

View 3 Replies View Related

Changing A Range Of Cells Backround Color With Vb

Oct 20, 2008

This is my first post and i am new to visual basic in excell even though I got a little bit knowledge of visual basic access, please do not get annoyed at me for making stupid mistakes.

I wrote the following ...

View 9 Replies View Related

Macro For Changing Background Color Of The Cells

Oct 21, 2008

I have an excel sheet in which many cells have a background color of Green. These cells are located all over the sheet. I want to change the background color from Green to Red not affecting the color of other cells in the sheet.


I will be realy really thankful, as I have to do this exercise for more than 50 worksheets.......If I start doing it manually by selecting each celll with green color background and changing it to red.....you can well imagine my condition.....my life would get shorten significantly while doing this.

View 6 Replies View Related

Changing Color Of Selected Range Of Cells Using Macro

Jun 17, 2013

I'm trying to create a macro that will change the color of the cells I've selected to green. My selection will vary depending on what cells I'm trying to color green (not a fixed range). My current code only changes one cell of my selected range:

Sub IN_PCA()
'
' IN_PCA Macro
'
'
ActiveCell.Select
Range("M243").Activate
With Selection.Interior

[Code] .......

I've tried using "ActiveRange" in lieu of "ActiveCell" as well as other commands that would seem to be correct but have failed.

View 3 Replies View Related

Changing Cell Color Based On Changing Values

Sep 16, 2009

This may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.

View 2 Replies View Related

Excel 2007 :: Changing Bar Series Color To Adjacent Cell Font Color

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

Macro: Changing Color Of Coloumn Header Based On Rows Color

Jan 15, 2009

I wrote a macro to color the cell values in the rows based on their average value. For eg if the cell value is less than 0.2 Avg, they should be red color,if value is between 0.2 and 0.5 it should be yellow. This part is working fine

Now based on the color of the rows cells , need to write a macro for the header one. Logic is Coloum header should be in red colour, if in one or more number of rows cells are red. same with yellow ones. Could you please help me out in solving this with logic.

View 8 Replies View Related

Code Color Cells

Aug 21, 2007

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

View 9 Replies View Related

Color Code Cells Appearing More Than Once

Oct 7, 2006

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.

View 9 Replies View Related

Color Code Protected Cells

Oct 23, 2007

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

View 4 Replies View Related

VBA Code - Changing Formatting Of Adjacent Cells At Varying Places

Jul 31, 2012

I have a macro that, at varying places, changes the formatting of adjacent cells. The formatting code is easy to generate via the macro recorder, but it's long - longer than the macro itself - and I'm using it a couple of times (due to if statements). That makes it difficult to read. It's usable, but looks ugly. I'd like to streamline its appearance.

Is there a way to do something like:

Code:
If range("F5").Value > 0 then
Activecell.Offset(1,0) format = blah
ElseIf Range("F5").Value = 0 then
Activecell.Offset(-1,0) format = blah
End If

The formatting I want is dark gray interior with a border box around it. The macro recorder generated this:

Code:
Sub test2()
'
' test2 Macro
'
'
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1

[Code] ............

View 3 Replies View Related

Code To Delete Cells Without Fill Color

Jan 25, 2013

I am looking for code that would delete all columns in a worksheet that do not contain a fill color.

View 9 Replies View Related

Color Code Cells Based On Value In Row 1 And Value In Cell

Dec 26, 2007

I 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

View 9 Replies View Related

Color Code Cells Based On Digit Assigned To Name

Jun 21, 2007

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 Related

Count Conditional Format Color Cells- Used Gantt Code

Sep 3, 2004

I've created a Gantt chart using the tip #58. Now how can I count all the cells that are colored?

View 9 Replies View Related

Excel 2010 :: Macro To Color Code Cells Based On Value In A Cell And Range In A Table

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

Color Changing Across A Row?

Dec 10, 2013

I am looking for a way to change the fill color of a row to just a portion of a row based on a on cell in the value on on cell in the row. I understand it should have something to do with ifthen but not realy sure of the exact phrases.

View 7 Replies View Related

Changing Tab Color With VBA

Jul 29, 2005

I am running a macro to copy the formats of one sheet to the next
sheet. One of the formats that I need to copy is the tab color. I
have the macro recorders code but it is for a specific sheet

Sheets("Jun-94").Select
ActiveWorkbook.Sheets("Jun-94").Tab.ColorIndex = 53

View 12 Replies View Related

Color Changing In Vba

Sep 16, 2009

I added a text box to my sheet with this

View 5 Replies View Related

Color Changing Formula

Jun 19, 2007

Need the formula or redirect me to a thread about changing the text color of numbers based on value.

I.e. I need for negitive numbers to be RED Positive numbers to be BLACK and 0 to be YELLOW like below.

1.0
0.0
-1.0

This formula will be use for a huge data base and only one column needs to be affected by it. Currently I had click the option to change color but it becomes a hassle and there is room for error. I wish to put the data in the column and not worry about the color.

View 9 Replies View Related

Changing Color Of Gridlines

Feb 22, 2008

how to change the color of gridlines from black to blue or red?

View 9 Replies View Related

Changing The Color Of Worksheets?

Mar 9, 2014

I got 2 questions:

1) How can I change the font color and background color of multiple worksheets at the same time?

2) How can I change hyperlink color?

View 4 Replies View Related

Changing Cell Color?

Mar 26, 2009

I have a code that check the String on the cell! But if it is wrong i wanna make the cell Red colored?

View 5 Replies View Related

Changing Color Of Rows

Mar 14, 2008

I have a spreadsheet where you can choose one of two options per row.

The listbox says either CASH or CHECK. If the user selects CASH, I would like the row to stay the same color. If the user selects CHECK I would like the row to be highlighted or change color. Upon a glance, anyone who knew the system could then tell how many people paid by CHECK, and who they were.

View 9 Replies View Related

Color Changing Per Month

Oct 8, 2008

I would like to know if there is a way to change the color of certian cells by entering in a month. Example. I would type in 10/10/08 and the cell would change color to brown. Not dure how to go about doing this or even if it can be done.

View 9 Replies View Related

Changing The Cell Color

Jan 27, 2010

I'm trying to change a cell color depending on another cells color. This is what I have so far:

PHP
Private Sub Worksheet_Change(ByVal Target As Range)    If Target.Column = 5 Then        Customer = findit(Selection.Value, Range("L:L"))        Colour = Range(Customer).Interior.ColorIndex        If Colour = 3 Then            With Selection.Interior                .ColorIndex = 3                .Pattern = xlSolid            End With        End If    End IfEnd Sub 

findit is another function I found on the internet:

PHP
Function findit(v As Variant, r As Range) As String    findit = ""    For Each rr In r    If rr.Value = v Then    findit = rr.Address    Exit Function    End If    NextEnd Function 

Everything works, except it just wont change the cell color. I have tried loads of different ways,
Target.Interior.ColorIndex
Selection.
ActiveCell.
Range().

If I put a msgbox in the If section it shows up so everything is working, besides the color.

EDIT: im using this with data validation list if that makes a difference.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved