Conditionally Set Of Cells Based On Different Cell?
Jan 10, 2012Is it possible to conditionally a set of cells based on a different cell? Example; Background color of cells A2:A10 = blue if A1>0
View 1 Replies
ADVERTISEMENT
Conditionally Formatting Range Of Cells Based On Formula?
Jan 18, 2013I had a list of people on sheet1 of my workbook, and on sheet 2 a table with a list of names and their department.
I started off by wanting to use VLOOKUP within the conditional formatting feature of Excel to find anyone who worked in "finance" in sheet 2. I could get this formula to work on the sheet but as soon as I put it into the conditional format feature it wouldn't work.
Trying to find a work around I added a column in sheet 1 where I inputted the VLOOKUP formula and caused it to display "1" if the person worked in finance. It looked like this:
A: Person's name
B: Value of 1 or 0
The idea was to hide column B and use conditional formatting to say "if column B2 = 1 colour column A2 blue". Now I could get this to work on a single row but couldn't make it work over a range, ie. "if cells A2:A150 have a 1 in the column next to them colour the relevent cell blue"
I feel I'm missing the obvious. I found quite a few answers online but non actually specified how to get the conditional formatting to recognise the relevent cell it should be looking at in a range.
1) How would I get conditional formatting working over a range
2) Whether it is possible to use VLOOKUP within the conditional formatting function of Excel.
Conditionally Format Currency Based On Another Cell?
Feb 28, 2014What I am trying to do is create a multi-currency expense report. For example, cell B1 has a drop down list of three different currencies. Based on the currency selected in B1, I want C1 to reflect that number formatting using the proper currency.
Im sure there is an easy way to do this but I have tried using an if statement in the conditional formatting section but it does not work.
how to total the different currencies using the ifsum formula, so this is more of a presentation task, but still it has annoyed me to no end.
In the example workbook, the currency value selected in row E should effect the currency formatting in Rows F-L
Conditionally Format First Cell Based On Values In The Row
Apr 29, 2009i've been trying for teh last two days to figuire out a way to conditionally format the first cell in a row to turn a certain color based on the values of several independant cells in that row, as opposed to monitoring the whole row. I have tried numerous IF, AND, OR, formulas to no avail. The cell obviously also needs to ignore blank cells...Can this be done?
Basically I want the first cell to turn yellow if dates in certain cells are within 90 days of today, and turn red if within 30 days of today.......
Conditionally Hide Rows Based On Cell Value
Sep 29, 2006I'd like to hide rows based on the value of a specific cell (that specific cell will always be C14, and it will have up to 8 variables selectable from a drop down list).
For example, if C14 = Innovative/exploratory, i'd like to hide rows 15-45. If C14 = Technical Service, i'd like to hide rows 15-32, and 42-44. I'm not familiar with VB, but am a quick study -
Conditionally Lock Row Based On One Cell's Contents
Feb 6, 2008I'm trying to figure out how to lock a range of cells (all in a row) when some puts a specific character in one of the cells. I have a sheet where I want people to enter data. Once they put a 'C' in the last column to indicate that the entry is complete, the whole row should be locked so they can't change any of the cells for that entry. There are some good pointers already on the forum to help me get the basics:
Conditional Cell Lock Unlock
http://www.ozgrid.com/forum/showthread.php?t=66397
http://www.ozgrid.com/forum/showthread.php?t=76379
However, all of these point to specific cells rather than a range of cells that can be located close to the target of the change.
A Cell Colour Based On 5 Other Cell Colours That Have Been Conditionally Formatted
Jun 30, 2009Using 2007, I need to conditionally format a cell colour based on 5 other cell colours that have been conditionally formatted. The 5 other cells will be coloured either red or greem. What I want to do is have an overall status cell that would be show green if all of the other 5 cells were green, amber if 4 of the other cells were green and one was red and red if 3 or more cells are red.
View 4 Replies View RelatedConditionally Format Cell Based On Another Cell That Is Manually Colored?
Nov 24, 2013let's say cell A1 is colored blue manually, how can i format cell b1 to be colored red? is it possible in conditional formatting?
View 7 Replies View RelatedConditionally Change Number Format Based On Another Cell's Value
Dec 1, 2009Format the number in Column AL to '[hh]:mm' when Column C's value is ="P/T", otherwise format to 'General'
Column C's value is referenced from another sheet in the same workbook via a VLOOKUP function.
Column AL's value is based on an IF formula which goes like this:
=IF($C4="F/T",SUM($AJ4)+($AK4/2),SUM($D4:$AH4))
Basically the above formula asks if Column C's value is F/T then count (because if F/T then the corresponding values in that row are whole numbers). If not, then SUM (because if P/T the corresponding values in that row are set to [hh]:mm format).
I have another sheet in the same workbook which has code (quoted below) which does something similar but I don't understand it enough to get it doing what I want for the sheet in question.
Conditionally Show / Hide Combobox Based On Cell Value
Jun 5, 2013I'm quite new to VBA, but I am attempting to get a Forms ComboBox to appear or disappear based on whether a certain cell (P7) reads YES or NO. P7 in turn updates in turn based on a user-selected value. As of now, the ComboBox only appears or disppears if I go back in and out of the formula I entered into P7. Basically, I want my ComboBox to dynamically update based on the value in P7. That may sound a little muddled, so here is my code for the ComboBox:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("P7")) Is Nothing Then
If UCase(Target) = "YES" Then
Me.Shapes.Range("Drop Down 30").Visible = msoTrue
Else
Me.Shapes.Range("Drop Down 30").Visible = msoFalse
End If
End If
End Sub
Conditionally Format A Cell In A Grid Based On Other Cell
Sep 12, 2006way to conditionaly colour code all cells in a grid based on cells in other grid: i.e.
gridA
1 1 2
1 2 1
2 2 1
grid B
12 11 14
34 16 53
35 64 23
i want all cell in grid B to be red if the coresponding cell of GridA is 2.
so here "14" "16" "35" and "64" will be red!
Conditionally Format Entire Row Based On Adjacent Cell Values
May 24, 2013I have a large workbook with 2 rows of data I want to format (R25:R530, M25:M530). Row M is already populated, row R is new data that I am entering. When the value of a cell in row R is larger than its adjacent cell (R25:M25) in row M, I would like the font to turn red. When the value is smaller, I would like the font to turn green.
With the amount of time I've spent attempting to conditionally format, I could have gone through and formatted each cell individually by now!
Count Cell Colour On Conditionally Formatted Cells?
Aug 5, 2014spread sheet that I need to do for work. Unfortunately I do not have excel on my home computer to be able to attach a spread sheet
The spreadsheet has one column (A) with a list of questions. The column next to this (B) has either a red or yellow cell in each row (a red cell would be a high risk to the business if the answer to the question in that row was no, and a yellow cell indicates a moderate risk to the business in the answer to the question in the row was no).
The third column (C) is conditionally formatted so if a 'y' was placed in any of the cells they would turn green. If 'n' is placed in any of the cells the cell would change to either red or yellow (this would depend on what the colour was in column B).
I need to know a formula to count cells by colour. So the number of red, yellow and green cells in column C would be counted automatically into a totals box for each colour at the bottom of the spreadsheet. I have tried some online suggestions but couldn't get these to work for cells that had been conditionally formatted. I'm not the most experienced person with spreadsheets (this time last week I couldn't add two cells together)
The final thing I need from the spreadsheet (and I'm not even sure if this is possible) is for a total box to be colour co-ordinated based on the number of red, yellow and green cells in column C. I would need the total box to be green if all column c is green, yellow if three or less cells in column C are yellow and red if any of the cells in column C are red or there are more than 3 yellow cells in column C.
Conditionally Hide Rows Based On 1 Column
Nov 22, 2007I am trying to hide rows based on the contents of column BS. It the cell is empty (not just zero value, but completely empty of all formulae), then I want to leave it unhidden, then if the cell value is less than the value in cell E2 I want to hide the row. This code doesnt seem to like the first if...then next i
Sub hiderows()
Dim i As Long
Set v = cell.Value("$E$2")
' Turn off screen refresh for speed
Application. ScreenUpdating = False
' Get last row Col BS
For i = Range("BS2000").End(xlUp).Row To 15 Step -1
If IsEmpty(Cells(i)) Then Next i
ElseIf Cells.Value(i) < v Then Rows(i).Hidden = True
End If
Next i
' Turn on screen refresh
Application.ScreenUpdating = True
End Sub
Conditionally Locking Cells
Feb 23, 2008I need to lock a cell (F21) if J26 is less than 100. Can't work out how in conditional formatting. Is there a different way?
This is what I need to do:
F21=(IF,J26<100,Locked,Unlocked)
Conditionally Delete Cells
Dec 14, 2006If you look in the file you will see a records sheets and a form sheet. I want to see if a record has been broken. All the times are in seconds and the distances are in centimeters. How can I find a certain row, compare and act based on an if statment?
View 2 Replies View RelatedConditionally Importing Data From One Of Several Different Worksheets Based On 1 Factor?
May 22, 2014I am currently working on a spreadsheet that records data from 11 different locations, with each location having it's own page within the workbook.
All of the pages are identical in terms of how the data is stored, the only difference is literally the name of each page (i.e. the location).
What I would like is to have a master page that then pulls through the relevant data, dependent on which location I choose from a drop down list on this page. As each individual page is identical in terms of lay-out, one formula will work for each page, with the only obvious exception is the page reference embedded in the formula. I know how to do a standard lookup for various figures, but I can't figure out a formula that would work with 11 different options... Is there any way to accomplish this?
Conditionally Format Rows Based Duplicates Only Found In First Column?
Dec 6, 2013I'm trying to conditionally format rows of data based on duplicates in the first column, then filter the results. I have a table of data with mutiple variables assigned to different "headings" that looks a little something like this:
A
B
C
D
[Code]....
[selects the table, then GoTo Special selects the blanks, fills in the blanks with the cell above, then Copy and Pastes As Values the entire table again to fill everything in]
I’m stuck on how to input a formula into the conditional formatting window that will:
Format the text to white in columns A, B, and E based on there being a duplicate above that row in Column A only. i.e. conditionally format the values with a * below:
A
B
C
D
E
[Code]....
If I use the conditional formula I found: =A1=A2, then cell E3 gets made white text when I don’t want it to, hence the “referencing column A” part of the question (Column A is always a unique ID number whereas Column E can have a duplicate in the row above). [Edit: Why can't I type Enter or put a line break here... I'll try re-edit at home...] When I go to filter on Column C for YYY again, the conditional formatting needs to realise to un-white the text, which is my next headache because it means conditional formatting that acts relative to hidden rows as a result of a filter... Oh and did I mention the client wants this done in 2003? This is an afterthought though – as I can force them to use 2010 if need be
Using COUNTIF To Conditionally Format Cells
Jun 4, 2014I am attempting to create a staffing calendar that will use conditional formatting to alert the coordinator by highlighting the cell when certain thresholds are reached.
The spreadsheet has three tabs, with 'Master' being the final copy.
In the 'Magic' tab, I was able to get it work with a rolling count but I am having trouble recreating it when the count is stationary and not updating itself line by line aside the person's name.
What I'm preferably looking to have happen is for the 'Name' to light up when it appears more than 3 times in a week in the Day fields, more than 1 time a week in the O/C field and more than 7 times a month in the Night field.
Conditionally Formatting Cells When Selected?
Apr 18, 2012Column A = list of names
O1:P20 MAY contain names
If A1 = Fred
AND O5, O16, P3, P19 = Fred
Selecting A1 changes all instances of "Fred" to RED-BOLD
If A2 = Felix
AND O9 = Felix
Selecting A2 changes "Felix" to RED-BOLD
Etc...
Conditionally Format Range Of Cells From VBA?
Apr 28, 2012Is there a way to conditionally format a range of cells from VBA?
I want the cells to turn red yellow or green when i type into that cell r, y, or g.
I can easily select the range of cells, but dont know how to format them.
Conditionally Format Cells To Blank With VBA
Jun 14, 2014I have a problem where I need to answers from a look up table to return an empty cell if the vlookup table returns a 0 or #N/A. This has to be the case as the answers are used in the creation of a csv file.
It would be easier for me to put in conditional formatting rather rewriting 40+ formulas and copying them down 1000 rows.
Is there a method in vba to format cells to blank?
Conditionally Summing Non-contiguous Cells
Dec 11, 2006Lets say I have values in A1, A3 and A5 and I want to to build either a sumif or an sum if array formula to conditionally sum them. How do I do this, assuming I don't want to use VBA or named ranges, i.e. create an array of noncontiguous cells and evalute & sum them.
View 9 Replies View RelatedSelecting Conditionally Formatted Cells That Have Been Coloured?
May 1, 2014I am looking to produce a Macro to select conditionally formatted cell's from a worksheet, i got as far as selecting those cell's but i need it to only select cells that have been filled.
View 6 Replies View RelatedConditionally Formatted Cells Which Turn The Text In Them To Red If The Value Is Less Than 40
Aug 18, 2009I have a series of conditionally formatted cells which turn the text in them to red if the value is less than 40. This works fine.
Occasionally however a value of less than 40 will need to be entered along with the letter 'v'.
ie. 39v
I'd still like this to be coloured red, but it's obviously coming out as black.
Is there a way to sort this out?
edited to add : Im actually using a separate cell to enter the value 40 (as the value can change).
Conditionally Format For Sequence Across Multiple Cells?
Mar 14, 2013What I am attempting to do is have my spreadsheet automatically detect and highlight personnel working seven days in a row. There are 63 total columns, the first one being for the person's name. The next 31 columns are a full month's dates in which their day shifts are recorded, while the final 31 are the full month's dates for night shifts worked. Days worked are listed as a "1" in the cell beneath the respective shift's date, while days off go down as "3". Currently I am using conditional formatting with the following formula, which highlights cells in red:
=ISNUMBER(FIND("1111111",(H7&I7&J7&K7&L7&M7&N7&O7&P7&Q7&R7&S7&T7&U7&V7&W7&X7&Y7&Z7&AA7&AB7&AC7&AD7&AE7&AF7&AG7&AH7&AI7&AJ7&AK7&AL7)))
I created the conditional formatting formula to apply to the row for the first person listed, and then copied and pasted the formatting only for the rest of the personnel rows. The issue I'm having is that while it will highlight the seventh work days, it will often highlights all cells prior to the grouping which has seven straight days of work. It also tends to highlight single or multiple cells in red which do not fit the pattern, and whose rows may not even have seven straight days of work.
How To Compare Cells And Format Conditionally Over Whole Sheet
May 30, 2014I wish to compare two sheets [Sheet1 and Sheet2] within one workbook and format the second sheet conditionally, based on an exact match to the first sheet.
Cell by cell, I can add the specific conditional formatting rule to each cell but this is tedious and its hard to see where I have applied the conditional format and where not.
So I tried to construct a macro to apply to a range of cells.
I tried to assign the logical result of the compare test to a cell outside of the range[lets say on Sheet2, A1] , and then format the range cells based on that logical value. I think my error is in conditional format criterion but I am a bit stuck.
[Code] .....
Conditionally Unlock And Unprotect Certain Cells In A Column
Jan 19, 2012Any way to conditionally unlock and unprotect certain cells in a column. I have been unsuccessful with my attempts at customizing other people's vba code that is a little different than what I am trying to achieve. So here's what I would like to accomplish:
If A23 = "Please See DME", then unprotect (pw=richie13) and unlock D23, then protect (pw=richie13) sheet again
If A24 = "Please See DME", then unprotect (pw=richie13) and unlock D24, then protect (pw=richie13) sheet again
If A25 = "Please See DME", then unprotect (pw=richie13) and unlock D25, then protect (pw=richie13) sheet again
and so on and so on.
VBA Macro - Conditionally Pick Cells From Another Workbook?
May 23, 2012I have 2 workbooks:
-1st workbook, I am using a worksheet (let's call it workbook1/worksheet1) already running with macro. In this worksheet, there is a column "Protocol" where different protocols are named (some are repeated, not sorted)
-2nd workbook, there is a worksheet (call it workbook2/worksheet2) are just the list of description for each protocol listed in workbook1/worksheet1. For example: width, length, height
How do I get values found in workbook2/worksheet2 and use in workbook1/worksheet1 for each corresponding protocol?
Copy Conditionally Highlighted Cells And Their Fields To Another Worksheet?
Jan 26, 2013I'm currently working on a maintenance task sheet for a couple of generators. I have successfully created button macros to input dates. I would like a macro to copy all the cells that are RED (due to a condition rule) plus their associated fields to the left from worksheet "Schedule" to worksheet "Tasks Due". [URL]
Here's the pre-programming flow, I just don't know the code to do it:
Scan worksheet "Schedule" for red cells in following Range
H8:I16,H18:I22,H24:I35,H37:I41,H43:I49,H51:I59,H61:I72,H74:I75,H77:I84,H86:I89,H91:H100
If cell = red Then Copy Row A:I
Insert Row into sheet ("Tasks Due")
If red cell is in worksheet "Schedule" H8:I16 then insert copied cells in worksheet "Tasks Due" one below cell = Fuel System
If red cell is in worksheet "Schedule" H18:I22 then insert copied cells in worksheet "Tasks Due" one below cell = Lubrication System
[Code] .....
If my uploaded workbook doesn't work I've linked screenshots of my document.
Top of "Schedule" worksheet: [URL] .....
Middle of "Schedule" worksheet: [URL] .....
Bottom of "Schedule" worksheet: [URL] .....
"Tasks Due" worksheet: [URL] .....