Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Row Change The Colour If A Particular Cell In That Row Contains One Particular Word

If A1 contains 'good' then row A = red

I need an entire row to change colour if a particular cell in that row contains one particular word.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Change Row Colour When A Value Is Given In A Cell
I dont have that great knowledge in Excel but i wanted to try and run a
macro in an IF formula but realised that was not possible. I have now got a
formula that gives a value in a cell when i want the whole row to turn to
red, but im not sure how i can get a macro that will change the whole row to
red when that cell shows a value. Can anyone help on this matter?

View Replies!   View Related
Auto Change Row/font Colour And Move Row
Attached example sheet. Basic table of data, with column F being a validation list, is it possible that when choosing COMPLETE from the list, the row and font change colour, and then move to the top of the list?

I'm not the only simpleton using the sheet so I need it to be as simple as possible. I know it doesnt seem like a big thing but the sheet we are working from is huge and I dont want people cutting and pasting away, I just dont trust them with my spreadsheets.

View Replies!   View Related
Created Validation Drop-down List - Can I Make The Row Now Change To The Colour That Relates To The Selection From The List
I am trying to create a fairly simple spreadsheet with about 8 columns and about 400 rows. One of the columns features a drop-down list with about 8 or 9 different options. Dependant on which option is selected, i would like the entire row to change colour with that option.

For example:
FAILED - whole row changes red
SUCCESSFUL - row has no fill
Tested - row changes to orange


Is this possible within Excel 2003?

View Replies!   View Related
Colour Range Of Cells In A Row Based On Value Of One Cell
I would like to change cells A2 to G2 to a different colour based on the value of G2

(0 to 48) Dark Green
(-2) Light Green
(-4) Yellow
(-6 to -48) Red

The range of the sheet would be A2 - G130. Each Row would change on value of cells in Column G.

View Replies!   View Related
Highlight Entire Row When Word Is In Cell
What I want to do is select all rows that contain the word conveyor in it. So far I have managed to select all the cells, I have also managed to select the entire row but one row at a time, I have even been able to turn all the cells a different color but I just want to highlight them for other formatting, copying, and several other things that need to be done. I have other documents this could be very useful in too so i want to make it a generic multi-line highlight rather than adding the formatting into the formula.

View Replies!   View Related
If Cell Contains The Word Delete Entire Row
Looking to write a macro to delete an entire row if the word GROWTH is found in any cell under Column C.

View Replies!   View Related
How To Search For A Word In A Column And Have Entire Row Opaque Once Word Is Found
I need my macros to search for the word "Cancel" or "Cancelled" in columns "T" and "U". Once found, I need the macros to make that entire row an opaque shading.

There will be other wording in these cells that contain "Cancel" or "Cancelled". Is it possible for the macros to search in the sentence and find the words "Cancel" or "Cancelled"

I started on the code below but am stuck.

View Replies!   View Related
Copy The Entire Row If The Cell In Column Has The Word
I need a formula to check sheet 1 column N and to copy the entire row if the cell in column has the word NO.

The row has to be extract in sheet 3.

View Replies!   View Related
Color Row If Cell In Column Contains Specific Word
I've been trying to come up with a conditional that will format a row if a cell contains any instance of a given word.

I know the following will format rows containing the EXACT entry of "apple" in column E:

But I'd like a conditional that will find entries like "apple pie" and "crabapple" as well. I'm sure it's a simple solution, using a SEARCH function or something...but I can't figure it out.

View Replies!   View Related
Color Entire Row If Cell In Columns Contains Specific Word
how to format a row based on text that is in a certain column. For instance, if a cell in column B has text in it I need to format the entire rest of the row with a certain background color and text color. I've been searching around here for a while and I think I'm really close, but I just can't seem to nail it down. Here's the code that I've worked on so far.

Sub Reformat()

Dim SrchRng3
Dim c3 As Range

Set SrchRng3 = ActiveSheet.Range("B1", ActiveSheet.Range("B65536").End(xlUp))

Set c3 = SrchRng3.Find("Europe - *", LookIn:=xlValues)

View Replies!   View Related
Range: Format The Entire Row For Any Row That Contains The Word "total"
I would like to format the entire row for any row that contains the word "total". When I run the following, it fails on the line with ActiveSheet.Used Range. Here is the code (pulled off of another post):

View Replies!   View Related
Look For A NO In Row And Change Cell
Take a look at the attached. I am putting together an assessment, I some questions require multiple responses to be addressed.

View Replies!   View Related
Row Will Be Inserted Above The Row With The Word "end"
when you run the macro AND when the word "end" is in any cell in column A then the following two things will automatically occur.
1. A row will be inserted above the row with the word "end", and
2. The formula in the "above row" in column B (right cell and above to cell with "End" word in it) will be dragged down into the new column.

What I want are the formulas in all the cells in the "above row" to be dragged down into the new row - not just column B.

Option Explicit
Sub InsertRowAtEndAndCopyColumnBFormulaDown()

Dim lInsertRow As Long

lInsertRow = Columns("A:A").Find(What:="end", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
Rows(lInsertRow).Insert Shift:=xlDown
Range("B" & lInsertRow - 1).Select
Selection.AutoFill Destination:=Range("B" & lInsertRow - 1 & ":B" & lInsertRow), Type:=xlFillDefault
Range("B" & lInsertRow).Select

End Sub

View Replies!   View Related
Change Row Color If Cell Value Is
I want to change the row color after the value in one of the cells equals a certain value. What is the best/easiest way to accomplish this?

View Replies!   View Related
Change Reference Cell From Column To Row
I am using this formula


It looks for the sheet based on A5
It finds the max number from the sheet in Column D

This is what i would like to do please....

On this found sheet - Column A has dates in this format

I would like to enter a date in a cell (say for example AA5 on main sheet)

Can the formula above be adapted to:

Look for sheet based on A5
Look at date entered in AA5
use Vllookup to find row containing date in AA5 (making table range the ENTIRE SHEET found based on A5)
Return value from Column D

View Replies!   View Related
Change Background Of A Row When Cell Is Selected.
My question is relevant to the one in this thread: ttp:// Instead of one cell, as in the above thread I want it for a complete row.

View Replies!   View Related
Change Row Based On Cell Update
I`m trying to work out the best way to propogate a cell's format accross an entire range. What I ultimately want is for a user to only have to change the text colour of one cell, and for the entire row to immediately change to the same colour with it. Even if this means always having to update for example the cell in column A to change the text colour accross that row.

View Replies!   View Related
Change Color Of Row When Cell Is Selected
I have a spreadsheet containing many rows of data that I need people to review. After reviewing the data in the column, a reviewer must enter comments in the right-most column and then hit the Enter key to move to the next row down.

What I'd like to happen -- As a reviewer selects the cell where they need to enter data, I want the entire row to highlight. I can use conditional formatting to change the color of the row once data is entered, but I want to change the color of the row when a specific cell is selected. I've attached a sample spreadsheet.

View Replies!   View Related
Match Amount And Colour Row
I am trying to match by amount with 1 column to another in a different sheet. If Data in column A in worksheet 1 matches data in column b worksheet 2 then color the entire row in column A worksheet 1 red, I am matching by amount so it(amount in column a) can be within 1 $ of amount in column b worksheet 2 also color item in column b(worsksheet2) red if matches with column a in worksheet 1 both can have a threshold of 1$ difference

View Replies!   View Related
Change The Row Colors Contigent Upon Cell Values
I know this can be done, as I have seen it before, but can't seem to figure out how to replicate it.

I have a speadsheet in which I have a drop-down box for a certain column's values. How do I automatically have each independent row's background color change dependant upon the value selected in that row's drop-down cell box (and update color automatically if a different drop-down selection is made later)?

For example:

Row 1: Cell C Drop-Down Value = "Yes"...change row color to GREEN
Row 2: Cell C Drop-Down Value = "No"...change row color to RED
Row 3: Cell C Drop-Down Value = "Maybe"...change row color to YELLOW

View Replies!   View Related
Conditional Formatting - Colour A Whole Row Of Data
I am trying to colour a whole row of data (in columns A through to AA) based on the numeric entry in column AB. The numeric entry is either 1, 2 or 3. I reckoned I needed to use the IF function, but as writing formula is not my strong point I am struggling to enter the correct data! The first row of data I wish to use is 3. I have tried the following but to no avail: =IF($AB3,1). =IF($AB3=1). =IF($AB3="1").

View Replies!   View Related
Colour Row To Denote It's Use In Autofilter
I am using autofilter and then copying some cells from the rows that are selected as a result and pasting them to another worksheet within the workbook. My problem is that in the worksheet that contains the autofilter I need to colour (or index?) the row that I am copying to show that it has been used. I tried UsedRange but got in a muddle.

View Replies!   View Related
Change Cell To Any Month And Recognize If Any Cell In Row
Column A = Row Description
Column B = Control value
Columns C-V = Time in Months

Row 1 = Months
Row 2 = Initial Values to find if >0

Cell B4 = Assumption of 18 months
Cell B6 = Value of 250 to insert if TRUE

In Cell C2 (January) I have a value that is 12
For example purposes I have an If formula in T3 (which is 18 months in the future. The formula is: =IF(C$2>0, $B$6,0). The result should be 250.

What I am looking for is a formula that will allow me to change Cell B4 to any month I want and still recognize if any cell in Row 2 is >0 then 250 (B6). All of row 3 should be filled with the same formula.

This could be a match if >0 then edate to column that has value >0, at 18 (B4) periods out.

View Replies!   View Related
Auto Change Row Color After Cell Data Input
Not sure if this is possible or if I would need to run a VB macro. image multiple columns with text headers.

'A'=Project Number 'B'= Date 'C'=Description etc etc etc 'S'=Cancelled (i actually use up to 'Z' on the columns for various other data)

I have added a Conditional Format on the cells in column 'S' that if the cell data = Y then colour the cell RED. This at first glance will show that the project has been cancelled. What I would like it to do is to colour the complete row (shaded grey for example) this would then show up more easily with the amount of data that is on my sheet.

View Replies!   View Related
Conditional Formatting: If A Cell Value In Column X Contains The Word "open", Format Row Background To Yellow
I need to set up some conditional formatting on my spreadsheet, however I am having a little trouble with one of my conditions. The requirement is:

- if a cell value in column X contains the word "open", format row background to yellow.

- if a cell value in column X contains the word "resolved", format row background to green.

- if a cell value in column X contains the word "moved" OR "closed", format row background to blue.

Now I have manged the first two on my own, using the conditional formatting tool and using the formula "=SEARCH("open",INDIRECT("X"&ROW()))".

However I am stuck on the last one. I tried...

=OR(SEARCH("resolved",INDIRECT("X"&ROW())), SEARCH("closed",INDIRECT("X"&ROW())))

However this doesnt work. I tried looking at adding VB script in but to be honest I am not a VB programmer and cant really spend too much time on this. fix the final conditional format so it run if the cell contains either "resolved" or "closed"?

View Replies!   View Related
Automatically Populating A Row Of Cells With Background Colour
I have five possible reps names in column A, when i type in "peter" i want that row up to column I to have a background colour of yellow, each rep must have his own colour. when i clear the name from column A the row should clear the colour. using conditional formating i can only get the first cell to get a background colour.

View Replies!   View Related
Spinner Link Cell Doesnt Change When I Copy And Paste The Row
I have a Piece of Code that copys a Row in my Spreadsheet and Pastes it underneath the original Row.

The Problem that I am having is that within the Row it Copys there is a Spinner in it. I want the Spinner to be copied also but the Cell that the Spinner is Linked to Says as the Original Cell, it doesnt move Down with the New Row Pasted in.

Here is the code that i have written to Copy the Row and Paste it down a Row.

View Replies!   View Related
Change The Colour Of Cell
I am tyring to do conditional formatting whereby if anything is entered in a cell i want the colour to change to say yellow and if nothing is entered just to leave blank.

View Replies!   View Related
Change A Colour Of A Cell
How can you change the colour of a cell (A1) using that number


1=red2=orange3=white4=light blue5=Blue12345


Make the cell with numbers above 0 go red and all cells with numbers below 0 go blue

View Replies!   View Related
Using VBA To Select A Row And Colour Code It Based On Conditions
I've had a look through the forum and can't see an answer, so apologies if I'm duplicating something!

I have a spreadsheet where column O has five conditions:
1 PO in
2 High Probability
3 Medium Probability
4 Low Probability

I need to colour code the whole row based on the value in the cells; if there were four conditions, I could do this with conditional formatting, but with five (with white as the fifth condition), I need to look at VBA.

However, I'm having difficulty colouring the whole row; how do I do this?

e.g. what I need to do is:
If O2 = "1 PO in"; Select A2:T2; Colour green

In addition, I need to ensure that if there is no data in column D, but column O is "1 PO in", it is highlighted in a separate colour. I have a macro for that, but don't know whether in needs to be put into the code before or after the macro needed above...

View Replies!   View Related
Paste Formula Across Rows But Change Relative Cell Reference To Column From Row
I'm working on setting up a chart. The formula I'm using for cell B21 is this: =ROUND(F3,0) & " " & IF(E3<F3 ...

View Replies!   View Related
Change Cell Colour On Delete
I would like to have the colour of a cell change within a spreadsheet anytime that a change has been made. I have used the worksheet_change function to change any text that has changed in a cell, however I would like to have the actual cell colour change if someone deletes anything from that cell. I figured that I could use the worksheet_change function for that as well however it is not working.

View Replies!   View Related
Change Cell Colour When Clicked
I've found a great thread that answered part of my problem (Change colour of text on update of cell)

This code changes the colour of a cell when selected. With the application I'm trying to make I need this effect to only occur in a certain area say A10:D30

I'm trying to make a scheduling page that uses timebars to represent when people are working. I though if a user could click or highlight the hours and a time bar produced I could lookup to see what hour the timebar starts and ends and show the hours worked. I also need it to cycle through 5 different colours before returning to white.

Private Sub worksheet_change(ByVal target As Range)

target.Interior.ColorIndex = 4

End Sub

View Replies!   View Related
Change Cell Colour On Doubleclick
I have the code below which fills a cell when doubleclicked. I would like to modify this to altenate between 3 different colours on each doubleclick.

View Replies!   View Related
Change The BG Colour Of A Cell When Selected
I have 5 cells in the same row (C20:G20)
When i click on one of them i want it to either change the background of the selected cell or the background of the cell below it.

EG if i click on C20 either C20 or D20 the highlight black and change the text colour to white, or just change the background to grey.

i thought it might be something like Target.Offset(1, 0).BGColor = 6

i tried this>

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells = C20 Then
Target.Offset(1, 0).BGColor = 6
End If
End Sub

View Replies!   View Related
2007 Cell Colour Change
Trying to have this embedded in the worksheet private sub change section. I want it to change the value of these ranges when the target has a value in it. I'm getting an error though:

If Target.Address = "$E$27" Then
If Target.Value "" Then
Range("I25:J26,P25:P26,R25:S26").Interior.ColorIndex = White
Range("H25:V31").Interior.ColorIndex = 15
End If
End If

View Replies!   View Related
I Want A Certain Object To Change Colour When A Certain Cell = Something
I dont understand how this event works.

I want a certain object to change colour when a certain cell = something.

View Replies!   View Related
A Macro To Delete A Row That Does Not Contain A Given Word
I am trying to find a macro that deletes a row that does not contain a given word or string.

For example, if a row does not contain the string -> then delete it or even better select and move all such rows to another worksheet so that the main worksheet is left only with rows containing the string.

View Replies!   View Related
How To Change A Cell Colour Depending An A Different Cells Value
How to a change a cell colour to say red in B6 if cell b12 = 1 and if e6 = 1 to change to green. I thought I might be able to use conditional formatiing but no. I had set the spread sheet to do a cell just for a condition representing a sum from another worksheet and it was working fine but i have to incorporate the two together and am stock.

I have attached a spread sheet.

View Replies!   View Related
Change Colour In A Cell - Conditional Formatting
I want to change the colour in a cell depending on the sum of two other cells.

So i want if


Is there any formula to do so with conditional formatting?

View Replies!   View Related
Colour Change Cell If Number Entered
I want to incoroprate into my Workbook a sheet with a list of phone numbers, that sheet can be added to and more numbers entered if necessary and will be called "DATA"

The questions is this. If one of those numbers in the "DATA" sheet is entered in any other sheet in the workbook can the cell that number has been written on (not the one on the data sheet) be made to change colour?

View Replies!   View Related
Conditional Formatting: Change The Cell Colour
I am trying to setup a monthly calendar.
What I want to do is have it so that if you type "apples I want some", the cell changes colour to green, and also the apples doesnt appear in the cell. So when you press enter, all you will see is a green cell that says "I want some"

I was wondering if there was a way in VB within the worksheet to make this happen?

View Replies!   View Related
Change The Colour Of Another Cell Dependent On Dates
I'm looking for a formula that will change the colour of another cell dependent on dates.

Say I have a date (or any data) in cell A1 I want cell A2 to be highlighted yellow, after 10 days I want this cell to change to red as it is overdue.

Finally when a date is entered into the yellow/red cell I want the colour to remove.

View Replies!   View Related
How To Change Cell Colour After A Year Has Passed
I am trying to devise a service schedule that has the installation date in say cell D4 and I would like this to automatically place a date a year from there in to cell E4, then 4 weeks before date comes round i would like the text cell to turn yellow, then on & after the date to turn red. Is this even possible?

View Replies!   View Related
IF Function To Change The Colour Of The Text In A Cell
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.


View Replies!   View Related
Change Cell Colour When The Protection Is Locked
Is there any way to change the pattern color of a cell (or a group of non-adjacent cells), say from No Color into Red if the cell(s) protection is locked (or unlocked)? Seems to me that the Conditional Formatting cannot work this way.

View Replies!   View Related
Change Cell Colour Dependant On Text
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 Replies!   View Related
Change Cell Colour Based On 3 Values
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 Replies!   View Related
Change Cell Colour If The Value Is A Minus Figure
Am after the code to change a cell colour if the value is a minus figure. There is a formular within the cell that can either equate to an positive or negative figure .. and would like the cell changed to RED if the figure returns a negative answer.

View Replies!   View Related
Formula To Change Cell Colour :: Shade A Cell Depending On Condition
Is it possible to use a formula to shade a cell dependent on a condition? I have tried an if formula (see below) but it is incorrect. =if('November 2006 SVOC'!B6>'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [red],if('November 2006 SVOC'!B6<'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [blue],))

View Replies!   View Related
Copyright 2005-08, All rights reserved