Apply Conditional Formats To Cell Based On Entries In Another?
Jan 27, 2012apply conditional formats to a cell, based on the entries in another, different cell?
View 2 Replies
ADVERTISEMENT
Excel 2010 :: VBA To Apply Cell Protection Based On Conditional Formatting Result
Jan 27, 2012I am trying to use the status of Conditional Formatting to toggle on/off protection for a cell.
I have a cell with Conditional Formatting applied if the result of a formula is true. If the Conditional Formatting is applied, I want the cell protection turned on so a user can not change the entry in the cell. If Conditional Formatting is not applied (false), cell protection is turned off and the user may edit.
I am using Excel 2010 and Vista.
Sum Based On Conditional Formats
Sep 11, 2008I have to color direct payoffs and commissions (green and blue respectively). I have to have a running total of payoffs that have not cleared and one for commissions. I have set up conditional formatting to color the text based on the check classification (A for payoffs and C for commissions). What I am trying to do is write a formula that will count only the payoffs (green text) that do not have a cleared date. I am then trying to do the same for the commissions (blue text). these totals are needed separately so I do not need them combined.
I have tried sumif and sumproduct. I have since deleted the formulas i have written cause they did not work.
Countif Based On Conditional Formats
Oct 16, 2009I need to count the number of rows of active clients. I have set up a conditional format that when someone goes inactive dependent on the reason of inactiveness their entire row of information either gets greyed out or a red line through it. Is there a way to count the current "active" clients- or those that do not have these conditional formats applied to them. And this is also dependent on month, so that it does not show the same number each month.
Apply Same Formats To All Sheets But One
Jan 18, 2008I'm trying to apply some settings to all sheets in my workbook except the first sheet called Total. For some reason, the settings are not applied to all sheets but simply stays on the sheet I select.
Also, how can I ensure the changes are only applied to the sheets: A, B, C, D etc. but not to Total?
Sub Test()
Dim ws As Worksheet
For Each ws In Worksheets
Cells.Select
With Selection.Font
.Name = "Calibri"
.Size = 8
Columns("H:H").Select
Selection.ColumnWidth = 35
Columns("I:I").Select
Selection.ColumnWidth = 12
Selection.NumberFormat = "#,##0.00"
Columns("J:J").Select
Selection.ColumnWidth = 12
Selection.NumberFormat = "#,##0.00"
End With
Only Apply Conditional Format If Cell Val > 0
May 10, 2013I have a column in a sheet referencing data from a column in another sheet in my workbook. I want to apply conditional formatting on these numeric values, from red for lowest vals to green for highest. However the fact that my column is referencing cells that will not yet have data means that they are being pulled over as 0. I am not displaying zero values in cells but the zero vals are still being included in the conditional format. Is there any way to discount these zero vals?
View 2 Replies View RelatedApply Conditional Formatting If The Cell Value Is Between 300-500?
Feb 4, 2014 is possible to construct a conditional formatting scenario and at the same time apply that formatting if the cell entry is within 10% either way of the logical test?
For example if you apply conditional formatting if the cell value is between 300-500 is it possible to add in a 10% swing on each value?
How To Copy Values In Multiple Cell With Conditional Formats
Mar 9, 2014Here's my problem..
if i input 75 in cell G2, the value in A7:E7 will paste or will display in A2:E2
or
if i input 76 in cell G2, the value in A8:E8 will paste or will display in A2:E2
or
if i input 90 in cell G2, the value in A9:E9 will paste or will display in A2:E2
or
if i input 78 in cell G2, the value in A10:E10 will copy or will display in A2:E2
See my attachment.. sample prob.xlsx
Apply Conditional Formatting If The Cell Contains Validation List?
Jul 13, 2013I have a table. I want to apply conditional formatting to the entire table so that wherever a cell contains a dropdown list (validation list) the cell is formatted with a different colour.
View 3 Replies View RelatedApply Conditional Formatting To A Cell When It Is Left BLANK
Nov 30, 2008I'm trying to apply conditional formatting (shading) to cells that are left blank.
(Purpose: I am designing a research template for a client to complete with data and want the spreadsheet to show them where they've "missed a bit"!)
(When I go to the conditional formatting box, it asks me to specify when "cell value is"..."between/not between/equal to/not equal to" etc. But there's no option to specify when the cell is blank.)
Conditional Formatting - Cell To Change Color When Apply Specific Value
Dec 4, 2012I know how to set a conditional for a cell to change a color when i apply a specific value, but how about if i want to set E2 cell to change to green when i place a 'X' on F2?
Pretty much I want 2 columns that say Yes and the Other No. When i place a X on Yes that other cell turns green, if i place a X on No that other cell turns red.
Inserting Rows With Formulas And Formats Based On Cell Value
Jan 11, 2014I am attempting to create a template to make time and dollar budgets for various projects easier to create (20 plus projects a year with varying number of employees).
I want to have a template in which you input the number of employees working in a cell (IE. 3 employees), and with that the table expands to have enough rows to have 3 employees data input. However, only insert the number required but not overriding "totals" row below. With the new inserted rows I would like to keep the same format and formula for the row it was based on.
Example :
week 1 hours
week 2 hours
total
[Code].....
Looping & Conditional Format: Finds A "J" It Will Apply Conditional Formatting To A Row Of 4 Cells Directly Adjacent?
Feb 9, 2009I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
View 2 Replies View RelatedMore Than 3 Conditional Formats?
Feb 12, 2009I have a spreadsheet with a number of columns. In column D there are the following options: 2,3,4,C,T. I want to color the cell if there is data in the cell and column D contains a value.
So for cell L17, my conditional format I have =AND(D17=2,L17<>"") - - (RED), =AND(D17=3,L17<>"") - - (YELLOW), =AND(D17=4,L17<>"") - - (GREEN).
I need to color for values of C and T: but there are only 3 conditional formats permitted. Is there any way around this?
7 Conditional Formats
Jul 20, 2008I have a sheet that shows sun, mon, tue........,sat - all 7 days.
Under each day there are 3 cols so 21 in total.
Data is being entered into cells each day but as the week goes on it gets more difficult to match up the col & row, to many numbers.
What I would like is a way to highlight the whole range of cells per day in a different colour but only when data has been entered under a day, so if no data then no colour fill.
I only need 2 colours, 1 for sun, tue, thurs, sat &
1 for the remaining days.
=COUNTA($A1:$C20)>0
This works for one range as a conditional format but there aren't enough options to do the 7 days.
Combining 2 Conditional Formats
Mar 12, 2007how to combine 2 conditional formats?
I'm trying to shade every other row grey so its readable with this:
=MOD(ROW(),2)
But, at the same time, would like to highlight upcoming expiry dates with this:
=AND(A2-TODAY()>=0,A2-TODAY()<=30)
Dates With Conditional Formats
Aug 19, 2009I am trying to create an Excel spread sheet that keeps track of employees safety tickets and the dates they expire. Is there way to have a cell highlight to a color warning me 30 days prior to the due date listed?
Eg. John has first aid and expires on 11/15/2009. I'd like the cell that has this date to highlight red on 10/15/2009.
I have messed around with the conditional formatting and know how to format the cells color and font et., but I can not figure out the formula to use to get my results.
I am using Microsoft Office Excel 2003 (MS Office Standard)(SP3)
Multiple Conditional Formats (4+) Using VBA
Oct 10, 2008I would like to condition one cell to display various colors, based on the information in another cell. I have attached the file for an example. The cell to be conditioned is A1, and the cell that will specify its color is B3. For example, if the value of B3 is "Red Oak", A1 would be red, and if the value is "Maple", A1 would be green.
I have more than 3 of these that need to be formatted (conditional formatting limits to 3 formats), so I'm wondering if I can do an event macro to set the formatting.
Conditional Formatting, Any Way To Get More Than 3 Formats?
Apr 2, 2008Anyone figure out how to get around the 3 format limit for conditional formatting? If so, I'd really love to hear about how you did it.
View 9 Replies View RelatedMacro For More Than Three Conditional Formats
Oct 12, 2008I use Excel 2003 and am trying to put together an automatic macro to apply five conditional formats to a range of cells. The range is H3:H21 and is unnamed. The values in the cells are derived from formulas. These are the conditional formats:
If there is a 1, the background color should be gold/44 and the font should be bolded.
If there is a 2, the background color should be gray 25%/15 and the font should be bolded.
If there is a 3, the background color should be dark yellow/12 and the font should be bolded.
If there is an e, the background color should be red/3 and the font should be bolded.
If there is a 0 (zero), the font should be white/2.
From what I understand, I need to put the code in by using the "View Code" option in the tab sub-menu.
Not Paste Another Set Of Conditional Formats
Aug 4, 2009I have a code that copies a template and pastes it into another tab. However, it is copying the conditional formatting and pasting it as well. I already have conditional formatting on the other tab and do not want it to paste another set of conditional formats because the formula's are changing between the copy and pastes.
Conditional Formats Between Dates
Nov 22, 2007i have a register with items for calibration. I have a column with a date the item is next due cal which is 3 years from date of receipt manually input by myself. say (H64) due 20/11/2010, i would like the cell to be green if todays date is below the date due, but amber if todays date is within 30 days of being due and finally red if todays date is over. i have manged green and red but cant seem to get the between right for the amber.
View 8 Replies View RelatedPasting Conditional Formats Without The Conditions
Sep 21, 2009I have a spreadsheet containing all sorts of formulas & data in Columns A - H, and a formula copied down in Column I generates a number that drives a few conditional formats in the preceeding columns.
I'm trying to copy-paste the values and formats of Columns A - H into a new workbook with this pretty simple macro...
Range("A1:H195").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
When the formats are pasted across it's pasting the conditional formatting, but they're not displaying correctly because Column I isn't coming over to the new workbook.
Is it possible to copy-paste the cell formats as they stand regardless of whether there's any conditional formatting behind it?
Formula To Count Conditional Formats
Oct 10, 2006I'm trying to count cells affected by conditional formatting and I need help tidying up my efforts so far.
The numeric values in my table are formatted if they exceed values in corresponding columns on other tables, indexed via Hack #75. The logic for each cell runs someting like this:
if cell is numeric
if column in (E, M, X, Y)
if (value <c1 or > c2)
highlight in blue
end
else ' columns not in (E, M, X, Y)
if (value > c1 or < c2)
highlight in red
end
end
end
My attempt to put together a formula for each cell appears to work, it looks like this for cell B3:
'=AND(ISNUMBER($B3),
OR(
AND(.....................
More Than 3 Conditional Formats For Formula Results
Jun 1, 2007I have a range A1:D10 containing formulae. I want the cells to change colour depending on the formula result. Conditional Formatting will do the job but only up to three colour so I need to use VBA because I have 6 colours. For example, if the result is 1, the colour changes to blue, if the result is 2, the colour changes to green, etc
I have VBA code which will change the cell colour if I input a value, but not if the cell is relying on formula result. The formulae results are dependent on cells throughout the workbook. The formulae results can be letters or numbers, whatever is easier.
Apply Conditional Formatting Using VBA And UDF?
Sep 14, 2012I am trying to create a macro (which will go in an add-in, using Excel 2007) which will apply a custom format to any selected cells which have their formulae hidden (Format Cells, Protection, Hidden). A similar macro works fine for locked cells.
Here is the UDF I wrote, which returns True/False based on the Hidden status of a cell:
VB:
Public Function Hidden(Check_Cell As Range)
Hidden = Check_Cell.FormulaHidden = True
End Function
[Code]....
just place all three pieces of code into a module, and change the first line of the two macros to a standard "Sub Macro1()" type format.
Greater Than 3 Conditional Formats For Text And Numbers
Feb 29, 2008I have been using conditional formatting for a project in Excel 2007 but as the end users are using Excel 2003, I have had to switch to the following VBA solution as my requirements exceed the standard 3 available conditions. I have looked at using custom formatting but I need to format the cell colour rather than just the font colour.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("C19:IV384")) Is Nothing Then
Select Case Target
Case "0.5", "1", "U"
icolor = 38
Case "C", "M", "P"
icolor = 40
Case "A", "S", "D"
icolor = 36
Case "L", "UP", "C/E"
icolor = 35
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
* When the macro is run on one worksheet, formatting and values are replicated simultaneously on another identical worksheet (not necessarily vice versa).
* As well as formatting cell colour when containing a value, a border should also be added with different colours for the top, bottom, left and right border.
* When the cell contains no value, the borders should return to how they were previously.
Apply Conditional Formatting Once Across 168 Sheets
Jul 3, 2014I have a workbook that contains 168 sheets of data (it's an extract from a PM tool) which is effectively a status report from each project in our portfolio. Contained within each status report are some financial data that shows a Plan number and a Forecast number for which I want to apply conditional formatting to this section (this is the same section for each sheet), to all the 168 sheets without having to go individually into each sheet. I have searched here and all the varying responses to a similar situation as mine, do not cater for the number of sheets that I have. And I need to do this on a monthly basis at monthend. So in the example below I want to apply conditional formatting if the Forecast (Cols D & G) are greater than Plan (Cols B & E). Is there a way of doing this just with the conditional formatting or would it need a VBA script?
Col A Col B Col C Col D Col E Col F Col G
Financial Summary - Selected Project Currency: USD
Current year total cost
Overall project cost
[Code] .....
Conditional Formatting Apply To 2 Columns?
Dec 31, 2013I wish to have one conditional format apply to K and L. Presently it's L only where it works.
=OR(K1="",AND(K1="No",L1<>""),AND(K1="OJ",L1=""))
Why when I change the range from =$L:$L to =$K:$L does it not apply to both?
VBA Conditional Formatting - Apply To Two Columns
Jun 27, 2014I have a conditional formatting issue, using VBA.
My conditional formatting code works fine for one column; however, when I try to apply the conditional formatting to two columns, the code only applies to the first column listed. Additionally, I have two sets of "rules" that apply to the same column. One of the "rules" includes two columns. The other rule applies to only one column. I'm not sure if this is my issue, or whether I'm trying to apply the same code to two different columns.
My code is below.
Code that applies to column "M" only...
