VBA Conditional Formatting That Works With Reference To Another Worksheet
Sep 11, 2009
I need to create conditional formatting on a worksheet where I need to change the cell colour based on 6 cases.
The VBA that I have found does not work with referenced cells to another worksheet in the workbook which I need as all the data is referenced with calculations.
I also need to specify the cells that the VBA will apply to as I need to apply 6 or 7 different different sets of conditional formatting on the same worksheet to different groups of cells.
View 14 Replies
ADVERTISEMENT
Jun 3, 2014
i want to calculate percentage between A and B and highlight top 10 values in B.
as of now i am calculating % in C and applied conditional formatting in C. filtering C based on color i am formatting B manually.
is there any option to avoid this manual work.
Total countAlarm reported%
562545%
783545%
322578%
View 3 Replies
View Related
Jul 24, 2014
I'm using conditional formatting where one column of cells is referencing another column of cells. For example, I want cell K2 to highlight if the date entered doesn't match the date in F2. I then want cell K3 to highlight if the date entered doesn't match the date in F3. I want cell K4 to highlight if the date entered doesn't match the date in F4 and so on. I'm not having any issue putting conditional formatting on the cells/row references individually, but when I try to copy the formatting (by using format painter) to save time all of the cells in column K just reference the first cell in column F since the first cell in column K references that F cell but not any other F cell. How do I copy these row by row references between columns so that each reference is unique?
View 1 Replies
View Related
Feb 28, 2013
I have a row of data I want to be blacked out if there isnt a reference number in the first column
So if I type the reference number i can in theory then use the rest of the row but if there is no reference number in the first column i cant see any of the row ...
View 1 Replies
View Related
Dec 21, 2008
As shown in the image given below, I have 2 tables. In Image 1, Vacation details are being fetched from a different worksheets. There can be 13 possible values in a particular cell. These 13 entries are,
V
SL
CO
T
PH
W
ML
PL
AD
BE
CTW
EM
LOP
MAR
OTR
Excel Jeanie HTMLSheet2 *BCDEFGHIJKLMNOP2Feb-09*Feb-093MonTueWedThuFriSatSun*MonTueWedThuFriSatSun4**************15VSLCOTPH***23456786WMLPLADBE***91011121314157EMLOPMAROTR****161718192021228CTW*******232425262728*9*************** Excel tables to the web >> Excel Jeanie HTML 4
WHAT I NEED :
If the value in cell B5 is V then J5 is being colored with PINK background. Similarly if any cell in Image 1 has value SL ( for e.g. C5 ) then K5 is being colored with orange. Similarly there should be 13 different types of color coding for all the above values which I mentioned.
WHY I NEED THIS ?
As you can see in Image 1, If I apply these colors to Table 1 only, then the user will not be able to see the value V or SL or any values belong to which date ?
Note - I am doing this for the whole year. But if someone can send me VBA code then I can do it for the rest.
View 9 Replies
View Related
Jun 29, 2006
I have made a sample file with conditional formatting. The file contains 3 sheets which contain a number of orders. Conditional formatting is changing colour of the whole line depending on value in column C. The formatting contains reference to a certain cell in column C. It is very easy to make when the lists are so short: I make conditional formatting for line 2 with reference to cell C2, copy formats in the other 2 lines and change formula references to C3 and C4. However in the original spreadsheet there might be more than 5000 lines per sheet and it will be too time consuming to correct the formulas manually. Does anyone know how I can make a reference to column C without specifying each particular cell?
View 3 Replies
View Related
Aug 28, 2009
I have a bunch of images which I want to be able to display in different cells depending on certain values or conditions. Say for example you have a drop down box with a list of fruits. When you pick a fruit, it displays its picture. Is this possible? If all of the pictures exist somewhere on the sheet, can you reference the pictures to have them appear in a cell rather than having images always "floating" over the sheet?
View 3 Replies
View Related
Sep 26, 2006
I'm using Conditional Formatting for an entire column to check the value of each cell and compare it to the value of a cell in the same row but a different column. (Cell Value equals =$D2) It works great until I insert or delete a cell in the formatted column. The reference does not change as one would expect. I've played around with formulae such as =$D2<>$P2 but the reference only changes for the P2, not the D2. I've also tried using =CELL("contents", ADDRESS(Row(),4)) but this causes excel to complain.
View 3 Replies
View Related
Oct 24, 2011
To protect formulas and formatting, I have code, which copies a row, with formatting and formulas, from a hidden sheet, unprotect the main sheet, inserts the copied row and reprotect the main sheet. This works great, except with conditional formatting.
My current conditional formatting works on the range: $A$3:$B$100. The copied row is inserted on Row 4. When done, this splits the conditional formatting to: $A$3:$B$3 and $A$5:$B$101, whether or not the copied row contains the same conditional formatting. This makes sense, but is it possible to maintain the original, all-encompassing range? Otherwise I'll end up getting thousands of conditional formatting for each insert.
View 12 Replies
View Related
Aug 18, 2009
I have a series of tabs with data, 25 columns by 600 rows, all with numbers. A cell should be red and lined out if its' corresponding cell on the very last tab is less than 50. I've tried to name range the area on the last tab (RawBuyers) and use it in a Formula Is CF on the first worksheet, but I get errors.
I'm seeking help with a CF formula that I can apply to each tabs data range.
I've seen threads similar to my issue, so I apologize if this is redundant (but nothing I've found is doing the trick.)
View 9 Replies
View Related
Aug 24, 2007
I have a worksheet titled "detail" containing 3 cells which automatically and independently change colour subject to their proximity to various dates. On a new worksheet titled "overview" in the same workbook I would like to have one cell which changes colour if any of the 3 cells in the other worksheet are triggered.
View 2 Replies
View Related
Sep 1, 2006
I am trying to do a conditional format of 0 to 2 is red, 3 to 4 is yellow and 5 is green. However I also need to have a cell that is blank to remain white and this is the part I am having problems with.
I found the following code since I cannot use the standard conditional formating in excel since it needs 4 conditions.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("H3:H12")) Is Nothing Then
Select Case Target
Case 0 To 2
icolor = 3
Case 3 To 4
icolor = 6
Case 5
View 7 Replies
View Related
Feb 27, 2014
I have a worksheet with a lot of different conditions applied to a lot of cells. I'd like to do some housekeeping, but to do that, I would like a clear and complete list of all cells that have conditional formats attached to them, and what the formula/criteria is for applying said format. I don't even care what the formatting is, but that would be nice too.
So, when I go to my "Conditional Formatting" screen, I get something like:
Now, this doesn't show me much of anything about what the formula actually is. I need to select it and look around. I'd rather just get a full listing of what the rule says...
And where I might have the same rule applied to different ranges of cells And so on.
View 2 Replies
View Related
Jul 20, 2012
I have a Workbook (Test 1) with several columns A-S. I'd like to be able to continue to add data into Test 1, and have some of the data pulled into a new Workbook (Test 2). Basically pulling data from Test 1, cols. D, L, J, E, I, C, K, Q and S - into the new workbook Test 2.
Some of the columns in Test 1 have conditional formatting and data validations, but I need the data in these columns to be replicated into Test 2 - without having to copy/paste between the 2 workbooks.
I figured how to do this in Excel 2010, but my office is still using 2007 and I can't seem to get it to work....
View 1 Replies
View Related
Jan 23, 2012
I am trying to conditionally format using AND() to reference if there is a value in the top line of a table and the title in the right most column for each cell in the table, the formula I am using works when i paste it into cells (i get the correct TRUE or FALSE for each cell in the sheet) but is not working as a format formula, I dont get an error but the shading does not happen.
The formula:
=AND(OFFSET($A$1,0,(COLUMN()-1),1,1)"",INDIRECT("a"&ROW())="Item1")
View 5 Replies
View Related
May 21, 2009
I have attached a sample of the issue. The formula is found in cell B49. If I enter a gravity of 2.76 or higher in cell B47 I get #N/A in cell B49. Why will it not read any farther over than 2.75. I have been starring at this for awhile now. I might look pretty dumb after someone else looks at it.
View 2 Replies
View Related
Mar 21, 2007
This script works great in 2003 in formatting text brought from Access, but fails in 97 - unfortunately I don't have direct access to Excel 97 - can anyone with it (or with a compendious knowledge of the differences!)
View 9 Replies
View Related
Jan 25, 2014
The formula I'm using is
Code:
=SUBSTITUTE(OFFSET(A10,-2,0),VLOOKUP(OFFSET(A10,-1,0),prim!$D$1:$I$263,5),VLOOKUP(OFFSET(A10,-1,0),prim!$D$1:$I$263,6))
But it doesn't work at all on one worksheet, half works on another, and works sometimes on another.
In the worksheet attached called workbook 4, it works a15 sheet 2, but not a10 sheet 2. But that was not always the case. In the worksheet attached justification copy, it works in a14 sheet 4 but not in a10 but for a good while it didn't work in both. In my own private doc it doesn't work in both cases.
[URL]
View 6 Replies
View Related
Dec 2, 2007
Private Sub Zero()
If Range("G8").Value = "" Then
Range("G8").Value = "0"
End If
End Sub
I have this listed in as code for VBA. If I hit the "Run Sub/User Form" button on that screen, with the field G8 having nothing in it (having hit "Delete" just prior), it resets the field to "0".
But when I hit delete on the worksheet, the field just sits there......no zero, no nothing.
View 9 Replies
View Related
Dec 30, 2008
I am running into an odd issue and hope someone might be able to shed some light.
I have created several lists on one sheet in a workbook and have assigned named ranges to each individual list. So far, so good.
On various other worksheets, I have created Data Validation rules to allow users to select the relevant data from drop-down lists. (using formulas such as =List1, =List2, etc. in the Source box to capture the named ranges I had created).
Everything works perfectly...EXCEPT for one worksheet, which continues to give me the error message, "You may not use references to other worksheets or workbooks for Data Validation criteria." Can anyone explain why this error would only occur on one worksheet and work fine for all the other sheets? I'm perplexed!
View 3 Replies
View Related
Apr 30, 2008
I have searched the FAQ's but have not found a suitable answer to my problem. I have some code that works perfectly when it is run from the VB Editor but when I put it behind a command button it gives me an error almost straight away. I have read that when a command button is used the command button defaults the active sheet to the one that it is one therefore you always have to specify the active sheet but I have done this so am still confused as to why it is falling over. Below is my code, I have commented where it is tripping:
Sub FormattingAcutalReport()
Workbooks.Open Filename:="H:Risk ReportingDaily TemplatesMF Consolidated Risk DAILY LIVE DATA FROM BO.xls"
Workbooks.Open Filename:="H:Risk ReportingDaily TemplatesDaily Non Banks LIVE.xls"
Dim myBorders() As Variant, item As Variant
Set SEGNSEG = Workbooks("Todays Reports.xls").Worksheets("Seg and Non Seg Bank Summary")............................
View 2 Replies
View Related
Sep 16, 2013
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
View 2 Replies
View Related
Mar 20, 2013
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
View 3 Replies
View Related
Jul 20, 2014
I have a workbook with 1000+ worksheets, all of which have 3-letter names. On a master sheet, I would like to make a query of how many non-empty cells there are on a subsidiary worksheet. This works:
Code:
=COUNTA(ABC!A:A)
What I'd like to do from time to time is input in column A a varying set of 3-letter worksheet names, say
AAB
ABC
CDE
And have a formula in column B that converts this to
=COUNTA(AAB!A:A)
=COUNTA(ABC!A:A)
=COUNTA(CDE!A:A)
I've learned that simply substituting the cell references A1, A2, A3 for AAB, ABC and CDE doesn't work. What do I need to do to achieve this?
View 2 Replies
View Related
May 5, 2009
Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:
if(A1=blue background,"Yes","No")
or
if(A1=red text,"Yes","No")
etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.
View 5 Replies
View Related
Jul 5, 2013
Any VBA that I can put into a macro that will convert conditional formatting into fixed formatting..? So when the cell contents/formulas are deleted the formatting remains. Assume that the range I want to convert is A1:D200...
View 9 Replies
View Related
Feb 7, 2014
I need to reference the projected and actual expenses from the total on worksheet 'expenses' So i did so, however, if I change the drop down on the expenses worksheet to only display housing data, then the projected and actual expenses on my budget worksheet changes as well to the new data portrayed on the expenses worksheet.
I need to reference the cell, without it changing when I change the category display, but I need the cell to change accordingly if I enter new data in the overall tablet on the expenses worksheet.
Or do I need to create a seperate worksheet that has the data in and reference my cells on my budget worksheet to that new worksheet?
View 1 Replies
View Related
Feb 9, 2009
I 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 Related
Oct 14, 2008
I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.
View 2 Replies
View Related
Aug 7, 2012
I am attempting to modify the code below to, not only reference the ".Item(rCell.Value)", but also include it's formatting. The format for that referenced cell will not necessarily be a "certain" format..so I don't want to have to write out what specific formatting needs to happen.
VB:
If (.exists(Trim(rCell.Value)) And rCell.Offset(, 1).Value = "") Then rCell.Offset(, 1) = .Item(rCell.Value)
The entire code is as follows:
VB:
Sub FillVariablesOnlyBlanks()
Application.ScreenUpdating = False
Dim a, i As Long, rCell As Range, ws As Worksheet[code]....
View 9 Replies
View Related
