Change Cell Colour VBA Based On Text String?

Oct 11, 2011

I need to change the colour of a cell using VBA based on the input of a certain word. I have used teh below code but it doesn't pick up different variations of the word

Dim cell As Range
For Each cell In Range("E2:E500")
If cell.Value = "Check" Then[code]....

This code works fine (i have put it into worksheet change) when I use "Check and Mark and Chase" but some users are bound not to use capitalised first letters and on these occasions the cell colours are not changing.

How to add something to thie code above to make it work for any variation of capitalisation?

View 2 Replies


ADVERTISEMENT

Change Colour Of Cell Based On Text In Another?

Feb 1, 2013

I am trying to make a cell change colour based on the partial content of another cell.

For example, cell A4 has a alpha-numeric sequence in it and I want cell B4 to turn blue if part of the sequence is ABD or F&C.

View 1 Replies View Related

IF Statement To Change Colour Of Text In Cell?

Oct 28, 2012

I have 2 columns of figures. One column has a defined name of 'Estimated Value' and the other column 'Actual Value'

My IF statement should simply read "=If(Estimate>Actual,Text color RED, Text Color Black)

My attempts at finding the code for the True/False statements are legion and 'Conditional Formatting' does not appear to work

View 4 Replies View Related

IF Function To Change The Colour Of The Text In A Cell

Dec 14, 2008

how I format the IF function to change the colour of the text in a cell, I know how to use conditional formating but it wont do what I need it to.

The formula I'm using (See Below) only inserts the word "Red" or "Green" in the cell it does not change the colour of the text.

=IF(H6=A1,"green",))

View 9 Replies View Related

Change Cell Colour Dependant On Text

Jul 20, 2006

how hard would it be to chane the background colour of a cell when a certain word is entered into it? so someone would type 'james' and press enter then the cell would turn red....would only be in a set cell range..

View 9 Replies View Related

Change Colour Of Autoshape Based On Cell Value

Mar 21, 2012

I've seen examples of VBA that will colour autoshapes based on cell values. I have a map of the UK made up of autoshapes that have been edited to create a shape of each postcode area such as EX, GL, NR etc. Also I have a list of all these postcode areas and I have given them a zone number (1-7) depending where in the UK they are.

PcodeZoneDH3DL3DN2DT1DY2E4EC4EH5

At the moment I have set the colours using the fill option so anything that is a particular zone is a certain colour (i.e. zone 2 is green).

What I would like to do is to set a zone to a colour, so the map will update all the zones with the right colours.

I need to be able to read the zone number that is assigned to a postcode area and change the relevant autoshape to the correct colour so that any postcode area can be one of 7 colours.

I can't attach a spreadsheet with my map and postcodes otherwise I would have.

Is there an easy way i can read the zone number of each postcode area in the list and update that autoshapes colour? Would the autshape need to have a name so it know which one to update or is this not possible?

View 9 Replies View Related

VBA Change Cell Colour Based On VLookup

Oct 22, 2012

I have found this code that, with changes, I can make work with the cells and columns I am using. What I cannot see is how to make more than one cell change colour rather than just the cell in column J.

How can I change the code below to change several cells in the row? I will be using cells in columns A:F.

Data will be entered into the rows from A2 to E2, in F2 a vlookup will enter one of the values, "High", "Medium" etc.

Each of these values has a colour assigned. The code below, with alterations, will be able to colour the cell in F2 depending on which value is entered. I need the whole row from A2 to F2 to be coloured.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim ChangedRange As Range
Dim r As Long, iCol As Long

Set ChangedRange = Intersect(Target, Columns("F:H"))

[Code] ......

View 5 Replies View Related

Change Cell Colour Based On 3 Values

Jul 18, 2006

My question is i want a cell to have the colours:

Green=if within the max min range [as specified by user]
Amber=if the current value is less than the allowable slack level[again specified]
red=if the current value is less than or more than the min or max respectively.

View 4 Replies View Related

Change Text Colour When Entry Made In Another Cell

Jan 10, 2010

I am using Excel 2003, my problem is that I have cells D4 & E4 with red text, when an entry is made in B4 (say $640, the amount can vary) I want the text in D4 & E4 to turn black. I have attached a section of the document in question.

View 5 Replies View Related

Change Cell Colour Based On Expiry Dates

Nov 13, 2013

I am trying to change the colour of a cell based on the expiry date. I know I need to use conditional formatting but Im having trouble.

Colomn E from row 5 to 1200 have dates in them from as far back as 08/2013. I need to change the cells so they turn yellow after 28 days and red after 45.

E.g. 1. E953 has a date 07/09/2013, I need it to change colour as above, 28 days and 45 days.
2. E961 has a date 04/10/2013, I need it to change as above, 28 days and 45 days.

Can I formulate the entire E column so when I put a date in, the formula will automatically start.

All the other formula's I've seen involve starting from "today" or "now" .

View 9 Replies View Related

Change Cell Colour Based On Number Of Days Before Date?

Jan 17, 2014

creating a cell to show: no colour unless
date minus 90 days as amber
date minus 45 days as red

View 6 Replies View Related

Excel 2007 :: Get Cell To Refer To Date That It Contains And Change Cell Colour Based On That?

Dec 19, 2012

I work for a UK charity and have a list of funders in an Excel 2007 spreadsheet.

One of the columns refers to the date on which a new application for funding can be made to that particular funder.

In many cases new applications for funding can't be made for 1 or more years since the last application - sometimes as many as 5 years later. How to get a cell to refer to the date that it contains.

For example, say I have in cell A1 "The Acme Funding Organisation" and in cell B1 (i.e. the "Reapply when?" column) a date of 01/04/2013 (British date format, i.e. 1 April 2013) then what I want Excel to do is to look at the date in cell B1 and if that date has been reached to highlight the cell red. That way I'll know that the reapply date has been reached & that a new application can be made.

View 2 Replies View Related

Excel 2010 :: Change Colour Of Cell Based On Date In Other Cell?

Jan 10, 2013

I am trying to create a sheet in XL 2010.

In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.

Column B is when the weekly inspection is due.
Column C is when bi-weekly inspection is due.
Column D is when monthly inspection is due.
Column E is when 6 monthly inspection is due.

I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A

I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.

For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.

A1
B1
C1
D1
E1

Inspection Date
Weeekly Due
Bi-WeeklyDue
Monthly Due
6 Monthly Due

January 1 2013
Change red Jan 8 & return blank Jan 10
Change red Jan 15 & return blank Jan 17
Change red Feb 1 & return blank Feb 3
Change red June 1 & return blank June 3

Ive also attached the worksheet

View 1 Replies View Related

Change Colour And Values Of Cell Based On The Value In Adjacent Cell?

Nov 10, 2012

I need to write a condition in such a way that cell IF A1= "DBR","BPR","SLR","SKR" then change cell B1 to SPECIFIC COLOUR Each condition different colour. A1 values ("DBR","BPR","SLR","SKR") is a list of selectable values. if the condition is falls then B1 should be normal (white color cell) with 0 in it.

View 1 Replies View Related

Excel 2010 :: VBA Code To Automatically Change Colour Shape Based On Cell Value

Mar 2, 2012

I have a peice of code that i know is inefficient and it is in danger of becoming too large. I have a spreadsheet that has circles aligned to each cell. There are around 100 in total. The code changes the colour of the shape based on the cell value in which it sits. However, the code needs changing and also it does not automatically update the colour shape even though the cell value changes. I have to manually select a cell and then the formula bar and then press return for it to update.

I am using excel 2010.

This is the code i am using for each shape.

Code:
If Range("n12").Value = text Then
ActiveSheet.Shapes("Oval 250").Fill.ForeColor.RGB = RGB(255, 255, 255)
End If

[Code]....

View 6 Replies View Related

Change Ddd Formatted Cell To Text String

Feb 17, 2012

How to change a "ddd' formatted cell and change it to a text string (even if you have to use another set of cells)?

I have 3 columns of cells A,B,C.

Column A has dates in it.

Column B has the "ddd" format of the A cells (takes date from A cell and turns it into the day of the week...ex:2/17/2012 into "Fri")

Column C is the one I want to formatt whatever is in the B column into a string, such as "Fri" but with it being text instead of "ddd" formatted.

View 4 Replies View Related

Conditional Formatting To Colour One Cell Based On Text From Range Of Cells?

Oct 22, 2013

what i would like to do is change the fill colour of D68 if the word Air appears with in D5:D65?

View 6 Replies View Related

Correlation Between Colour Brightness And The Auto Change From Black Text To White Text?

Aug 16, 2013

So I have been playing around with the tab colors on my workbook and am trying to figure out the correlation between color brightness and the auto change from black text to white text. I've noticed that if the Green color value is higher, excel is more likely to use the black text. If Blue is high, white. Green takes precedence over Blue and Red is just kind of in its own world.how excel calculates this?

View 2 Replies View Related

VBA Code To Colour Certain Cells Based On RGB Colour Shown In Cell A1

Apr 17, 2014

I have a lookup that gives and RGB colour code in Cell A1; for example 186, 206, 140.

I'd then like Cells R10:V15 to fill with the RGB colour based on the result in A1.

View 2 Replies View Related

Search Cell For Text String And Paste Text In Based On Finding?

Jun 29, 2014

I need to create a macro to do the following:

Search the activecell for a text string (a), and then either paste in text string (b) at the end of the cell if (a) is found, or text string (c) if (a) is not found.

For example, if the activecell has "AA/" in it, I want the cell to become "AA/01" (pasting in "01" at the end), and if the cell has just "AA" in it, I want it to still become "AA/01" (pasting "/01" at the end). The macro will be linked to a commandbutton.

View 7 Replies View Related

Change Text Colour If Value Is Calculated By SUM?

Jun 12, 2014

I need to see easily if my cell is giving a value by using a SUM. This is so I can see if the SUM has been taken off or overridden by typing in a number.

View 4 Replies View Related

Change Colour Of Check-box Text When Checked

Jul 5, 2008

Qtn.1 - Do i use 'Conditional formating' under 'Format' to change colour of Check-box text when 'checked'
I.e when ticked >> change colour of text to red

Qtn2 - If yes-, what's the syntax to put in 'Conditional formating' ?

View 9 Replies View Related

Change Colour Based On Columns Filled

Sep 8, 2008

Really need a formula for a traffic light system
ie
if 3 colums have entries no
4 Maybee
5 yes
and it highlight it in green amber or yellow

View 11 Replies View Related

Text Colour Change Depending On Outcome Of Formula

Jul 6, 2009

i am looking for a formula, if i put it as plainly as i can then i hope someone can work it out for me!

if ad4 is greater or less than 30 then ad4 text is red, if ad4 is equal too 30 then text is green

View 14 Replies View Related

Change Colour Of Comment Text With VBA If Its Today Date

May 7, 2013

I have a number of comment (all column D) that have lots of data in each one.

The data will appear like this (each new entry (new entry denoted by a date))

01-May 12:58:05 Liam
02-May 16:18:27 Josh
07-May 11:51:26 Bob
02-May 01:13:34 Terry

What i want to know is, can i change the colour of the text in the comment, if its todays date. So in this case 07-May 11:51:26 Bob goes red (its the 7th here for me).

View 3 Replies View Related

Change Rectangle Colour And Font Based On A Vlookup Value?

Aug 13, 2014

I have an autoshape rectangle which is linked to a cell on my spreadsheet. This cell will auto update whenever i type a number into a different cell (match and index formula).

I want the colour of the rectangle to be green, amber. red or black depending the value my formula brings back. I have managed to do this but the colour does not automatically update. Also when the colour goes black i will need the font in the text box to be white, at all other tines it will be black.

View 12 Replies View Related

Conditional Formatting Colour Change Based On Dates?

Jan 8, 2012

I have a simple due date spread sheet for tasks that need completing at work. I am able to make the 'Due date' cell turn red when it is equal to or less than todays date, using conditional formatting. No problem.

I also have a 'Date closed' cell which is populated with a date when the task is complete.

What I am struggling to do however is change the red 'Due Date' cell to Grey once the 'Date Closed' cell is populated with a date. In fact I would like the whole row to be greyed out once the 'Date Closed' cell is populated.

I also have an 'Open / Closed' cell, which tells me if a task is open or closed, being chosen from a drop down I installed. Could this be used as an alternative to the 'Date Closed' cell. ie If someone selects 'Closed' in cell A4 the whole A row goes grey.

View 4 Replies View Related

Change Font Style / Size And Colour Of Text In Combobox

Nov 8, 2013

Is there any way to change font style, size and colour of text in a combobox?

View 1 Replies View Related

Conditional Formatting To Change Colour Of Cells Dependent On Text Within Them

Sep 8, 2013

How to colour cells depending on text in other cells, for example,

I would like cells D26:AA26 to turn light red if the letters 'FSM' are in cell 'E26' even if other text appears in it, eg 'SA+/FSM'
I have been playing about with conditional formatting but can seem to solve the mixed text issue?

I have included an example below,

Do you use a "*FSM*" around the formula?

[URL] ...........

View 7 Replies View Related

VBA To Change Background Colour In Free Form Depending On Text In Cells?

Apr 3, 2014

I have a spreadsheet with several freeforms. I would like them to change backgroundcolour, if the content in a cell is equal to another cell.

Basically I would like my shape "Freeform1" to change backgroundcolour to RGB (0, 180, 0) if Sheet1.Range ("A1") = Sheet2.Range("D3").

If it's not the same content in both cells, I would like the freeform to remain unchanged, that's RGB (79, 129, 189).

This should happend automaticly, so I don't have to click the shape to make it happen.

View 5 Replies View Related







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