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


Advertisements:










Change Cell Color If Another Cells Content Is Yes Or No


I have a large workbook with some tasks.

My problem is:

I have 8 columns with info.

In the second column i type a part number and in the third one i type "Yes" or "No".

I wanted to make the second columns cell turn red if the third column's value is Yes or white if the value is No.

Is possible to do that with the help of a formula, conditional formating or vba?

I prefer to use a formula or the conditional formating.

I searched the net but i found a solution if the third column contains a number not a text.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Change The Back Ground Color On Basis Of Content Of Cell In A Range
i had to change the color of cell to yellow if the cell content is "N/A"

i had write few lines but don't know how to move in a range cell by cell

View Replies!   View Related
Change Cell Color Based On Cell Content
I have a chart full of different names which I need to separate them by color when a specific entry is found in a Cell. For example the list has the store name "ZELLERS" + its branch number. (ex. ZELLERS #276 PL or ZELLERS #295 SL and so on). I would like to modify the below code so that It only check for the name ZELLERS and not the branch number. If it finds ZELLERS in any cell In the Range of C1:C500 then color it Red.

Below Code does that but I have to write the exact name as it appears in the cell otherwise it wont find it for me. Is there any way to resolve the issue? I am not an Excel Programmer but Only an Excel user.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("C1:C500")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
On Error GoTo Endit
Application.EnableEvents = False
vals = Array("ZELLERS #276 PL", "ZELLERS #295 SL", "Gopher", "Hyena", "Ibex", "Lynx")
nums = Array(3, 46, 6, 3, 7, 4, 20, 10, 23, 15)
For Each rr In r..........................

View Replies!   View Related
Conditional Cell Color Change For Multiple Cells
I'd also like to have it be able to change the 2 cells to the right, in columns (M&N) whenever the conditional value given below are true. I thought I had posted this message yesterday but never saw it show up as a thread.

Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("L1:L800")
For Each Cell In MyPlage

If Cell.Value = "Hold" Then
Cell.Interior.ColorIndex = 3
End If
If Cell.Value = "Complete" Then
Cell.Interior.ColorIndex = 4
End If
If Cell.Value = "Issued" Then
Cell.Interior.ColorIndex = 43
End If
If Cell.Value = "Release" Then
Cell.Interior.ColorIndex = 36................

View Replies!   View Related
Change Color Of All Cells On Sheet Based Off Of One Cell Value
I want to change the fill color of all cells on my sheet, based on the value of one specific cell. In my sheet, I am using cell F1 as the trigger for the change. If the word Blue is in the cell, I want the background color of all cells to be Blue. Likewise for Red and Yellow as well. I don't believe conditional formatting can get this done, as all but the one cell (for this) will be empty.

View Replies!   View Related
Locked Cells :: Change The Cell Color On The Click Of A Command Button
i want the user to be able to change the cell color on the click of a command button. the worksheet is protected. when the user click the command button the active cell changes to red and offsets by 1. then the work sheet is locked again.

The two problems I am having is 1. I want the range to begin from row 10, column k to column FD. all cells down

the second problem is the current code allows me to edit locked cell columns A to J ...

View Replies!   View Related
Fill Color Depending On Content Of Other Cell
I have a template I have been asked to amend and I know I need to use Conditional Formatting I just can't crack the formula I need to use. Cell A2 will have either "CD" or "CW" or "IN" input into it

When user inputs either "CD" or "CW" into cell A2, I want cell G2 to have "Margin Movement" automatically displayed. When user inputs "IN" into cell A2, I want cell F2 to have "MV" automatically displayed, and I want cell G2 to be coloured in bright red fill. Then user is to go to cell G2 and input some text as a description, and I want the red fill to disappear once they have input something in there.

I thought I had it licked using IF statements for the "Margin Movement" and the "MV" cells, but I can't get the conditional formatting to work - I assume this is because the cells aren't technically "blank", as they have IF statements in them.

View Replies!   View Related
Change Cell Content
I have been supplied a list of names. The format is Smith J Mr, I would like this to read Mr J Smith.

Is there a command in excel I could use?

View Replies!   View Related
Change Font Color Based On Adjacent Cell Color
I have two columns. The first one (A) contains cells that have different Fill colors. The second column (B) contains text adjacent to the colored cells. I am trying to change the color of the text in the second column (B) to the corresponding color in the adjacent cell in the first column (A). I don't think conditional formating works well in this situation. I believe the solution would be some sort of macro.

View Replies!   View Related
Auto Calculate Color Function On Cell Color Change
I would like to be able to change the color of a cell in V4:AB31 and have the formula in AM10:AM13 automatically calculate the new result. As it is now the user has to press Ctrl ALT f9 for the formula to recalculate.

View Replies!   View Related
Change Part Of Cell Content
In Excell 2000, when you enter a cell by using either the cursor keys or the enter key, the contents of that cell are selected. Typing anything other than the 'F2' key deletes the current entry in the cell, replacing it with what you are typing. This is not the same as double clicking on a cell whereby the cell does not have any of it's contents selected so that when you type the keys are inserted at the cursor position

Is there any way in which this behaviour can be changed so that the first and subsequent characters typed after entering the cell are added to the end of the current contents of the cell ?

View Replies!   View Related
Change Font Color If Cell Color Become Yellow (6)
I have font color white in blank cells in column E and I (from row 5 to row 245) so the visitors will not see the text. If any of these cells become yellow (color code is 6), the font color will become black so visitors can see the text alot far better than white. I've tried this code myself after this post but nothing happen

View Replies!   View Related
Change Text Color Based On Cell Color
I have various row cells in column (F) filled with the color Green. And corresponding text in Column G. How can I change the text of that particular row to white.

i.e.: if any cell in column F is Green, change the text color of that row in Column G to white?

View Replies!   View Related
Change The Cell Color On Drop Down Change
I have a drop down sub pasted to worksheet:

Private Sub ComboBox1_Change()
ComboBox1.List = Array(100, 200, 300, 400)
If Range("I11").Value < Range("N11").Value Then
If Sheets("Profile").Range("K18").Value < ComboBox1.Value Then
Range("I11").Interior.ColorIndex = 2
Else
Range("I11").Interior.ColorIndex = 3
End If
End If

End Sub

I want it to change the cell color on drop down change. How can I modify things to have the change in drop down selection?

View Replies!   View Related
Change Color Of Blank Cells
I am wanting to change the background of a cell to red if the cell is blank - Basically this is to highlight in a form any missed important areas...


View Replies!   View Related
Change Color Of Cells Unless It Is Protected
I am trying to change the color in Range("A1:bb5000") for each worksheet unless it is protected.

Here is my code

Sub Color_Sheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range("A1:bb5000").Select
If Item.Locked = False Then
With Selection.Interior

View Replies!   View Related
To Change Color Of Cells Depending On Value
when I execute this code on the selected cells, it completely ignores my selection, and makes the entire worksheet black.

Sub ColorCells()

Dim cell As Range

For Each cell In Selection

If cell.Value = "U" Then
cells.Interior.Color = 3

View Replies!   View Related
Cells Automatically Change Color
I have been given a spreadsheet that turns whole rows different colours when certain data is entered into a cell. I want to locate the code and use it elsewhere, but cant find it?

I have looked at all of the change related procedures in the drop downs, for the Workbook e.g.

Workbook_SheetChange

but no matter where I cant seem to find any code at all.

Is there a way of exporting every line of code and then open this file in notepad to skim through it?

how I might find the code that is making the rows turn different colours?

View Replies!   View Related
Merge The Content Of 2 Cells Into 1 (with A Comma Separating The Content)
how to combine the content of 2 cells into one cell and have the information separated by a comma.

For example:

CELL 1:
Software 1

CELL 2:
Spreadsheet Software

The desired results is:

CELL 3:
Software 1, Spreadsheet Software

View Replies!   View Related
Change Text Color While Combining Cells
Is there a way to combine cells in excel and chage the color of part of it?

Example: On my sheet I have multiple cells I need to combine:
Say A1 reads 23.65
Say A2 reads 43.65
Now on A3 I want it to read "Estimated budget 23.65 vs 43.65"

I can get this to work but here is the problem, I want the 23.65 to always be red and bolded and the 43.65 to always be blue and bolded.


View Replies!   View Related
Conditional Formatting: Change Another Cells Color
Is it possibe to change another cells color (Fill) based on what is placed in that cell? Example: A1 =if there is an "x" in A2 highlight A1 Green, but I have information in A1 also. Or if I had a different cell say= if there is an X in A1 Highlight A1 Green ect.. This key is to highlight a cell that I have information in based on a different cell having an X in it. If ther eis no X in the cell I have indicated, nothing needs to happen.

View Replies!   View Related
Cells' Color Change Depending On Dates
I have column "C", starting from "C5" with dates till C200, but not all cells have dates some of them are also blank.

I want the cell to change color depending on the date.

If the written due date is in a month from today, I want it to turn yellow, and if the due date is in the past from today, I wanted to turn red. I want it to get updated every month.

View Replies!   View Related
Using Conditional Formating To Change The Backgound Color Of The Cells
I have been using Conditional Formating to change the backgound color of the cells, this works fine. However it cannot be used with a userform text box. There I believe that the use of If Then Else statements will sort out the problem.

I have developed three conditions:

1) D10 less than E5 result True
2) D10 less than F5 result True
3) D10>E5 result False

using that result background color to turn green

Next condition
1) D10 less than E5 result False
2) D10 less than F5 result True
3) D10>E5 result True
using that result background color to turn yellow

Next condition
1) D10 less than E5 result False
2) D10 less than F5 result False
3) D10>E5 result False
using that result background color to turn red

What I cant seem to do is combine the three instances above so that the cell back color changes.

This is so easily done using the built in conditional format function but I'm have a difficulty understanding how to make this work.

View Replies!   View Related
Change Background Color When Exceeding A Certain Number In Cells?
I got an excel sheet were in column A i have listed a few names and in column B I got their respective results (numbers) with a background color.

I want to insert a function in such a way that when their numbers exceed a certain number the background color changes from for example blue to green.
so >1200 = blue background color
1200-1350 = red
< 1350 = green

View Replies!   View Related
Clear Contents & Change Color Of Cells
how to clear contents and change colour index of cells.

This is for a booking system. The current VB codes allows bookings to be made by entering a reference number, dates of bookings and room type and bed type. When the VB codes are executed, the cells corresponding to the details entered earlier, would fill the booking table with the reference number and change the colour fill (e.g. yellow) to indicate that the slots are booked. Please refer to attached sample.

However, I do not know how to delete the booking. I would like to assign a cell for user to enter the reference number that needs to be deleted. So when the reference number is entered into that cell, user could execute a function which will clear the contents and change the colour index to none for that particular booking with that reference number(s).

Attached is a sample of the problem.

View Replies!   View Related
Change Color Of Autoshape Based On Comparision Of Cells
I am atempting to do some autoshape conditional formating. I am about above average when it comes to working with Excel, and I know little to some Visual Basic. Here are the two things I am attempting to accomplish.

1. I am attempting to use VBA to change the color of a Large Pyramid Autoshape called "JanPyramid". The thought behind this is, I wish to have the Pyramid show vbGreen when it is <= my target (which in this case is a user inputed cell called "RECTARGET", so instead of using a number in my VB code I would like to have it refer to this cell in E58) I want the Pyramid to show vbRed when the total Recordables (this number is generated by using a COUNTIF function, so it counts all entrys in the table that have REC entered for its status, in this case the cell is called "RECCOUNT" and is located in I54) is > my target, E58 "RECTARGET"

2. I have already sort of gotten this to work by using values instead of using cells. But it is still is not perfect. I would prefer to use the actual cells because my targets and the actual counts can be different depending on what happens during that month. Here is the code I have in so far, it is with the values instead of it being the cells because I do not know how to make it read the cells instead of values:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("RECCOUNT")) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Value > 0 And Target.Value <= 2 Then
ActiveSheet.Shapes("JanPyramid").Fill.BackColor.RGB = vbGreen
ElseIf Target.Value > 2 Then
ActiveSheet.Shapes("JanPyramid").Fill.ForeColor.RGB = vbRed
End If
End If
End If
End Sub

This code is a hybrid from this Ozgrid thread: Changing fill color of autoshapes based on cell numeric values.

View Replies!   View Related
Color Cells Based On Color Of Preceeding Cell
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.

View Replies!   View Related
Change Font Color In A Range Based On One Cells Information
I have attached an example file.. Basically I want the text in a range to change to red if one cell="Description".

Also, I want a cell's text to turn blue if it's corresponding data in the row says "Click on Title to Follow Link"

The example file explains it a lot better..

Thanks for the help.. Sorry if this may seem like a duplicate post but I was not able to get any of the examples I searched for to work that others have posted.. Also, maybe conditional formatting would be an option here but I could not get it to work as it will only meet the first condition that comes across thats true...

View Replies!   View Related
Read Cell Content & ADD Content + Font Properties *SOLVE
I use this to read cell content, add some text/characters (ie. [ and ]) and change the properties of the complete cell

Sub COMMENT()
Worksheets("DVD Lijssie").Activate
If ActiveCell.Value 0 Then ' Change all in to ... ... ...
ActiveCell.FormulaR1C1 = ActiveCell.Value & " " & "]" & " " & "["
With ActiveCell.Font
.Name = "Arial Narrow"
.Size = 8
.ColorIndex = 16
End With
End If
End Sub
HOW can I change this vba-code so it leave's the content of the cell like it is and add some content with the use of let's say TexBox1 and ONLY use different font properties for the newely added content?


View Replies!   View Related
Clear One Cells Content When Changing Another Cell
I am quite a novice with excel and I am trying to get a cell to show blank when I select a drop down list value in another cell.

Cell D2 contains a validated list containing two items (Air, Vacuum).

If I select Vacuum I wish to change another cells (D4) contents to blank, D4 is also a validated list containing three items (Yes, No, BLANK) the blank is actually a blank space and not the text blank.

I can get the cell to operate correctly but if I select Air in Cell D2 and then Yes in cell D4, but then change my mind and reset to Vacuum in cell D2 i need the cell D4 to clear its contents automatically.


View Replies!   View Related
Macro To Copy Content From Cells Into One Cell
I have a question.

What code do I need to make the data that is in more cells to be shown as data in one cell ?
ex. A5 D5 to be shown as D2(in a new workbook)
1 1 11

And that for every row?

View Replies!   View Related
Copying Many Cells, Drop Content Into One Cell?
I have a document I was directed to fill in. I was instructed to only use a certain number of characters and then drop into another cell below it for paragraphs of text. I have now been told they made a mistake, need me to copy all the related cells for a specific paragraph and drop them into a single cell.

Can I copy the text in multiple cells, and then drop the cell formating of those copied cells and drop the content into one single cell. Either word wrapping the content or soft returns for what looks like paragraphs again?

View Replies!   View Related
IF And Change The Color Of Cell
i need to add this in a macro that i previously created
here is what i need, i hope its clear for everyone to understand

I want to be able to change a color of a row to purple if a cell is greater than 5 days
so basically what i need is something like this

IF BQ1 has the data "APPROVED" and data in BP1 is 01/04/10 and BN1 is blank and if BN1 is blank after 5 days of BP1 then the color of this row changes to purple


View Replies!   View Related
Change The Color Of The Cell
I have to check a cell to see if ti has a date in it, if so , then i have to check the current date to another cell, if the current date is greater then that cell i want to change the color of the cell


View Replies!   View Related
Cell Color Change If Value Changes
I've got some spread sheets that I send out. People fill them out and send them back to me. The only problem is that I dont know which cells they've changed and which cells they've left. Is thier a way to tell VB to change the color of a cell if the value has changed

View Replies!   View Related
Change The Cell Color
When I click on a button I would like an option to cancel a row range of cells by turning the writing red with a cross through it.

So if I wanted to cancel a certain row of data I would click on a button, enter the range of cells to cancel and then this would turn all of the writing in the cells to red colour with a strike through the words.


View Replies!   View Related
Change The Color Of Cell
I am having some color problems. From A2:AB2000 I have different fill colors for cells all over the place. The only color I need is the color purple which I believe is the color 39.

So basically all cells except the cells with the color purple change to white from A2:AB2000. I would like to add this code to the macro I have already created.

View Replies!   View Related
Color Change In A Cell
What formula do i use if i want the text in cell A1 to change to red if any entry is made in cell B1.

View Replies!   View Related
Text Color Change - Cell By Cell Basis - When Budget Is Reached
I'm a business/application consultant for ERP software, and generally pretty solid when it comes to excel. However, I've come across a question for a personal sheet that I can't seem to solve.

In my personal budget worksheet, I'd like to set a budget on a cell-by-cell basis. When the budget is hit, I'd like the text color (or cell color) to change.

I.E. Cell D14 has a budget of $200. When I enter $200 in the cell, the text turns from black to red. I've searched through google with no luck. I have mildly searched this forum, but haven't found my direct answer - I have come pretty close though, in other languages

View Replies!   View Related
Formatting Row Of Cells Based On A Cell Content
I want to format a range of cells based on the name listed in a cell in column A (starting @A6). I currently do this manually. It is to help me visually see the line I am working with and for what person.

I am almost certain that is going to take a VBA, but I know little to nothing about setting one up.

I have attached a sample. I am using Excel 2003.

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 Cell Selection Color
I just loaded 2007, and among the things driving me nuts is I can't figure out how to change the color of highlighted cells that I have selected. The default color is so close to the normal background color(White) that it is difficult for me to distinguish between selected cells and unselected cells.

I've tried the built in help but couldn't seem to search for the right phrase to bring up a relevant answer. I've had the same problem when trying google and Microsoft's office 2007 help site. I also searched the forum before posting and came up blank.

View Replies!   View Related
Change Active Cell Color According To Name Above
Here is a screen shot of what I have:

I want to be able to enter the name from the above 4 cells (B3,B4,B5,B6) IN THAT COLUMN, and have the active cell change color accordingly.

If I am in cell C7 and start to type "PUBLIX" It should turn red in color. It does not because the code looks to a specific cell (example: B3). What can I change in my code to give the results I desire?

I have conditional formatting code below I copied from THIS site:

Private Sub Worksheet_Change(ByVal Target As Range)
' Multiple Conditional Format
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Adjust Format range to suit
Set rng = Range("B7:H74")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Adjust conditions to suit
Select Case Target.Value
Case Range("B6").Value
Target.Font.ColorIndex = 50 ' Green
Case Range("B5").Value
Target.Font.ColorIndex = 3 ' Blue
' Case "Super"
' Target.Font.ColorIndex = 6 ' Yellow
Case Range("B4").Value
Target.Font.ColorIndex = 13 ' Purple-ish
Case Range("B3").Value
Target.Font.ColorIndex = 5 ' Red
' Case "Corporate"
' Target.Font.ColorIndex = 37 ' Light Blue
End Select
End Sub

View Replies!   View Related
Click On Cell Change Color
In Excel 2007 I been scanning in the forum for example code. If a cell is clicked on Then highlight the that row from cells A to K. If it is clicked again change it back to no color.

View Replies!   View Related
Change Cell Color Based On Value
I'm putting the sum of 3 cells in a 4th cell(D2). If the sum is greater, I would like the sum to be the color green. If the sum is less, I would like the sum to be the color red. Is this possible?

(D2) has change value, I want to compare the new value against the existing one.

View Replies!   View Related
Hyperlink To A Cell To Change Its Color
I am trying to hyperlink to a cell, & i want to change the color of this cell after the hyperlink activated.



View Replies!   View Related
Change Color Of Active Cell
Is there VBA code that will:

a) change the color of the active cell?

b) return the formatting back when that cell is no longer active?

View Replies!   View Related
Change Color Of A Cell By Just Clicking On It?
I'm trying to do. I have 2 columns, both have empty cells with borders. I would like to make it so that when the user clicks one of the cells, it turns grey and the one beside it turns white. I set up two macros as follows:

View Replies!   View Related
Change Cell Color Relative To Another
I am in the middle of my ICT A-level coursework and im required to design a spreadsheet with a grade system (A to F). The students have individual target grades and recorded grades from tests and homework will also be dispalyed. How do i change the colour of the cells of tests or homework depending on what target grade the student has. I will have to do this for about a hundred students, so conditional formatting isn't really an option to do each individual cell. I'm not familiar with coding, but can learn. Not sure if a formula can solve this either.

I have used the search function of the forum for this question but have not found anything regararding this.

View Replies!   View Related
Change Cell Color Upon Entry
I want the entire row to turn green if a value is added/changet in anya cell in that row. I got the basics, but the macro changes color on the cell that is selected after the input, that is if I change A1 I want A1:J1 to turn green, but what happens is i change A1 and then select B5 than B5 turns grenn, I have tried to modify my macro but can't get it right ...

View Replies!   View Related
Change Active Cell Color
Found code on this site to change the color of the active cell. One problem and two questions. Code works, however, when you save and close the last active cell is stuck with the new color. Question1 is it possible to limit this code to cells in column B only and fix the problem above? Is there an easier better way to get the same result. See Attached

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved