Change Color Of Cell Depending On Values In Another Range
I want to change the colour of a cell depending on its value, when compared to another row of data identiified by a value in another cell. To try an clarify:
b3 = 1 b5=4 Because b3 = 1 then compare cell b5 with the row g1 as b3=g1 then depending on its postion set a colour
g1= 1 g2:g5 = 2,3,4,5,6
h2= 2 h2:h5 = 7,8,9,10
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
Change Color Of Cell Range When Last Cell Receives Data
I need my worksheet to automatically fill in a range of cells (from Range B*:G*) with my yellow color once the last cell in the range (G*) receives data, and automatically switch back to no color if the data in Cell G* is erased. However, if somebody merges a range, it should appear as it would by default - as if no macro exists, if that makes sense. I've attached a sample worksheet.
View Replies!
View Related
Color After Depending On 1st Value In A Cell
I have another macro request. I would like to grey out a certain qty of cells depending on the 1st number in another cell. So 1st of all it would have to check in column B to see if there is any data and then check for the first number in that cell (there will be many numbers in the cells but we only need the 1st one (Ex.: B3 = 3 / 8 & 8). Then it should start counting from column D the found number of cells multiplied by 2. Ex.: if Cell B3 contains 3 / 8 & 8 it will count 3 x 2 = 6 (3 cells x 2 = 6 cells) Then, if after that count (now at I3), in the next cell over (J3) if the is data in the cell above (J2) then it should apply an Interior.ColorIndex of 15 for the next cells until there is no data in the cells above (if nothing after K2 the grey will be applied to cells J3 & K3 only).
View Replies!
View Related
Changes The Cell Color Depending On How Many Clicks
The code changes the cell color depending on how many clicks you give it. The code it self works fantastic but the problem i have is if i manually unlock the sheet every time i click any where in sheet it locks it again. Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo Error Dim I As Integer, I1 As Integer, ws1 As Worksheet Set ws1 = Worksheets("36hr Plan") ws1.Unprotect "muppeticity" I1 = 40 For I = 5 To 25 Step 5 For I1 = 22 To 204 Step 13 Offset1 = 0 If Not Intersect(Target, ws1.Cells(I1, I)) Is Nothing _ Or Not Intersect(Target, ws1.Cells(I1 + 1, 5)) Is Nothing _ Or Not Intersect(Target, ws1.Cells(I1 + 2, 5)) Is Nothing Then.............
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
Cell Color Fill Depending On Condition Of 2 Other Cells
Afternoon everyone i am having abit of trouble working on an excel 2007 spreadsheet. In cell I1 i have a tab called Color. i want the cells below I1 to be filled with one of 3 colors green, yellow, or red depending on whats in cell F "Status" (closed or open - in progress) and cell G ECD for estimated completion date. Green - i need it to fill green if status is closed. Yellow - need it to fill yellow if ECD is any date greater than today and if status is open. Red - need it to fill red if ECD is todays date or older and status is open.
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
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
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 Cell Depending On Column Header
I have a spreadsheet with names and start / finish dates columns down the left hand side then a row of week commencing dates along the top. for each name row I would like to change teh fill colour of a cell to green to represent the week in which they started and to red for the week in which they finished. I therefore need to cross reference the start and finish dates for each name with the relevant week commencing dates at the top. Somehow! I presume there is some kind of vlookup type formula that I need to use in conditional formatting, but I am not sure what.
View Replies!
View Related
Change Cell Reference Depending On Date
The spreadsheet attached is a timesheet I use. The last column on the sheet (IV) keeps a running total of time spent on each project. I need a way that i can find out how much time was spent on projects in specific timeframes (eg 01/01/09 - 07/01/01, or 01/01/09 - 31/01/09). my initial thought is perhaps i can have a "from" cell and a "to" cell to input the dates i require and it will total the relevant cells in the rows below. NOTE! This workbook uses VBA to auto hide any column which are dates previous to today's date so you may need to unhide them to view times logged.
View Replies!
View Related
Prevent Cell Change Depending On Sum Result
In my spreadsheet cell g1 is a name and cells g3:g6 contain data for that name. I have a macro that exports the data in g3:g6 to another workbook and then deletes it. What I need is a macro that when I try to change g1, gives me a warning if there is unexported data in cells g3:g6 (ie the sum of them is greater than zero) and gives the option of either continuing or stopping (presumably using a YesNo box).
View Replies!
View Related
Fill Color Change With Range
Function BG(InRange As Range) Range("InRange").Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End Function That so far but not quite sure why that isnt working. I want to change the fill color with a UDF that all they do is select a Range and it changes those fill colors to whatever the Colorindex may be. I didnt find anything while searching the forums with this already.
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
Drop Down Lists - Depending On Other Cell Values
I have a workbook with several drop down boxes and formulas already set up and working. I want to improve it though. My question is.... is there a formula that will make the drop down list change based on a cell value.... For example: If B6 equals vegetables then C6 equals list (potato, carrot, pea, etc.) If B6 equals fruit then C6 equals list (apple, banana, grape)
View Replies!
View Related
Send Email Depending On Cell Values
Does anyone know the code to send an email to a set list of six recipients when cells in a certain column in a worksheet reach a certain value greater than another. Let me explain a little - The action to be performed is date-based. For Example: When the date cell L2 is 30 days later than the date in K2, send an email with subject containing data from cells B2 and C3. However, this has to work for columns K and L in their entirety, as both columns contain a list of dates...
View Replies!
View Related
Lookup Results Depending Upon Cell Values
Depending upon a figure entered e.g. = $ 895.00 and also Depending upon a state being entered e.g. = NSW. Then Excel will lookup a series of cells to produce the correct figure depending upon the correct data ciritera being met. I have a data table sitting behind the spreadsheet with all relevant values.
View Replies!
View Related
Setting Color Of Range Based On Adjacent Cell Color
This is probably elementary, but I'm struggling and would appreciate any help as I have very little excel VBA experience to draw from. I have assembled code which changes the cell color based on a value change in Column A. Column A will contain many different groups of repeating values. This code works well and and I have been able to figure out how to limit the number of colors to only 2. The end result is each set of similar values in column A is visually grouped by one of two alternating colors. The number rows in the data set is variable as the data set is extracted from SAP. The number of columns is fixed. What I want to do now is set the cell color in columns B through F the same color that was assigned to the row in column A. So if cell A3 is set to colorindex = 6, then I want to set the range of cells B3 to E3 to the same color. Here is the code I am using to set the color of the cells in Column A:
View Replies!
View Related
Inserting A Numerical 1 Or 0 Into A Cell Depending On The Values Of Two Other Cells
I'd like help writing a single formula that assigns either a numerical 1 or 0 to a cell based on values from two other cells. By way of background, I'm working with three columns. Cells in column F contains values from 1 to 4. Cells in column G contain values from 5 to 11. Cells in column H are currently blank. I need a single formula that will insert either a numerical 1 or a 0 into column H according to the following conditions.If F3 = 1 and G3 = 5 or 6 or 7 then let H3=1 otherwise let H3=0 If F3 = 2 and G3 = 7 or 8 or 9 then let H3=1 otherwise let H3=0 If F3 = 3 and G3 = 9 or 10 or 11 then let H3=1 otherwise let H3=0 If F3 = 4 and G3 = 9 or 10 or 11 then let H3=1 otherwise let H3=0
View Replies!
View Related
Count Cells Depending Upon Multiple Cell Values
In what would be Cell E2 I want to post the Total posted set Assuming that the Order Numbers and the OP Numbers are the Same, but only if there is a value in the allowed set, So I'd expect E2 in the case below to read 1.83, Order No OP Posted Set Allowed Set 30761157 00100.731.500 3076115700100.500.000 3076115700100.600.000 3076461000100.000.000 3076461000101.050.500 3076524400100.000.000 3076524400100.550.500 3076639600100.000.000 3076639600101.180.500 3076810900100.000.000 3076862900100.000.000 3076862900100.000.000 3076862900100.000.000
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
Color Range Between Specified Values
I'm using this conditional format formula (compliments of "shg") = COUNTIF($T$16:$T16, "R") * COUNTIF($T16:$T$600, "R") Which fills a color (Red) based on a specified value, up to the last entry. The conditional format is within a range ("Range1") in column P. The formula looks in column T for a value of "R" , in which it fills the cells between the first an last value with the color Red. as a brief example: Column..P,.....T 1..................X 2........Red.....R 3........Red.....X 4........Red.....R 5........Red.....X 6........Red.....X 7........Red.....R 8..................X 9..................X However my workbook has become very slow.. I have several columns utilizing this conditional format .(albeit trigered by a different letter value) Is there a way to incorporate this functionality into a macro, one which i can initiate via button assignment?
View Replies!
View Related
Sub Worksheet Change Range Color Error Due To Protected Sheet
I have this code that works fine until I password the sheet and the range (b9:d65536) is protected. When I protect the sheet and make a change I get a debug error. What would I need to add/change to handle protected cells on a protected sheet? VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes" ....
View Replies!
View Related
Change Range Format & Color Based On Result Of Drop-Down Choice
I'm using Excel 2007. I have a field with a "YES"/"NO" drop-down box. There are three fields below which have some default formatting (thick purple dot-dot-dash borders). When the drop-down field is "YES", those three fields should change borders to a single thin black bottom border line. OK... so that's the setup. And I should note here that it does in fact format properly. However, the problem I'm having is that the screen does not update / repaint with the new formatting. The thick purple dot-dot-dash borders remain, for the most part. If I page down, then page back up, the new formatting appears just fine. When I switch from "YES" to "NO", again, the formatting changes, but the screen does not update/repaint so the only way to see the new formatting is to again... page up and then page down again. I'm wondering if anyone knows a way to force the screen to repaint/update so that my users will see the new formatting without having to page up/down.
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
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
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
Conditional Formatting Conflict: Change The Cells Font Color And Bolding As Long As The Value Is Within That Date Range
I have two conditions setup in Options>View - Zero Values.", "style="background: #FFFFFF;padding: 2px;font-size: 10px;width: 550px;"");' onmouseout='GAL_hidepopup();'>formatting.htm" target="_blank">conditional formatting. The first is setup for alternate row coloring with this formula inside Conditional Formatting: Formula is = MOD(ROW(),2) My Second Condition is Cell Value is between $P$10 and $Q$10. This sets the font bold and a different color. The two cell values are two dates. I want to change the cells font color and bolding as long as the value is within that date range. It works fine, but for cells that are on the row that is colored the second condition doesn't apply for some reason.
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
|
TRACKER
