Format Range Based On Legend
Aug 22, 2008
I need a basic code that accomplishes the following:
1) The user can define a legend for which he can format the cells in that legend however he wishes
2) Cells elsewhere in the sheet that have the same value as that in the legend will format exactly how they are in the legend
3) Default if not in the legend
I found this code that is close...but apparently it relies on the user selecting the cells he wants to format according to the legend FIRST...my code needs an Intersect command or something like that.
Also im not sure if this code actually takes into consideration font+border+shading formats...my code would need to take those into consideration.
example of the desired output is attached! ....
View 3 Replies
ADVERTISEMENT
May 10, 2007
I think looking at the attachment might make more sense, but here is a brief description of what I want to do. I am trying to change the colour of a cell based on looking up the value contained in that cell in a predefined list (which in my example I have called a legend). I need to do this in VBa but my VBA is not at all good.
View 4 Replies
View Related
Aug 18, 2009
In this file i did paste special values so the links are no longer there, but in my original file alot of the values you see in the cells are pulled from other worksheets ( thats just so you know to lookin values of the cells and not the formula ).
I basically need row 18 starting from Column O to self populate.
Its based off of which has 1's in the Column. I have the completed here manually just to show you what i am looking for . I usually have to go to row 3 and Enter the data manually depending on which 1's are filled in for the column.
If i could have a method of having row 18 starting with Column O to self populate based off those 1's
View 8 Replies
View Related
Sep 8, 2009
I have a monthly calender, with each month on its own sheet. I have a sheet named Holidays, which list the holidays. I have been able to use conditional format and highlight the dates in the months the holidays fall on, but I would like to highlight a range of cells below the date on monthly sheet. I have attached a sample of what I have and would like to achieve. I am not much good at using VBA, but would not be against using it either.
View 2 Replies
View Related
Apr 9, 2009
Here is my code now. It loops through col E and formats corresponding rows in accordance with the E value:
View 7 Replies
View Related
May 20, 2009
I am working with the Conditional Formatting, which is fine for one cell. Here is what I am trying to do: IF cell in $A1 = 1 then bold $B2:$M2 and apply solid line border to top of cell ranges. I have tried conditional formatting but it only formats the cells in column A. And I can't seem to find a BOLD statement for the cell formulas.
View 2 Replies
View Related
Jul 1, 2008
I have been tasked with creating a Macro in order to help speed up a rather simple set of steps that are taken to format a spreadsheet each day. The last step, however, is not so simple. I would like to add a statement to the macro code that does that following:
Based on the value of a specific cell, color the cell and all corresponding cells within the same row (from colums A to AA) yellow. Essentially, cells in column D may have a the value "No Allocation", and if they do, I will need to format that cell yellow as well as all the cells within the same row for the colume range A to AA.
View 9 Replies
View Related
Jul 3, 2009
I am preparing a model for a client that runs excel 2003. I need to change the format of a range based on the value in a cell (reference cell) that is outside the range to be reformatted.
Reference cell is a named cell with the value calculated with a Match() function, the only possible values are (1, 2, 3).
I need the VBA code to retrieve the reference cell value and apply one of three formats to the range. I believe the code for that part is something like:
Private Sub Format_Font()
Set vrange = Range("d63:r63")
Set refrange = Names("ind_agentfee").RefersTo
Set refrange = Mid(refrange, 2)
On Error Resume Next
If refrange = 1 Then
With vrange.Font
.Style = "Comma"
.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Else
If refrange = 2 Then
With vrange.Font
.Style = "Comma"
.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Else
.Style = "Percent"
End If
End Sub
I am having problems getting the proper opening statements and declarations correct and also get an "Else without Then" error with all combination's that I am attempting. I am sure that the code is mess now as I started with a recorded macro then modified it multiple times while researching the web.
View 9 Replies
View Related
Nov 11, 2009
conditional format formula that is based on a text range within a cell. In my case, I would like to compare the low and high range when inputted as a number followed by the quotation marks followed by space then hyphen, space then number and ending in quotation marks. The quotation mark is being used to represent inches.
Like this in cell D14: 0.2” - 2.2”
I am using the following condition format formula which works for the strict case above.
HTML =OR(D16<LEFT($D$14,3)+0,D16>MID($D$14,8,3)+0)
However, there will be times when users using this spreadsheet may leave out the spaces on either side of the hyphen, or add more than one space between the number and hyphen. Also if additional digits are added, my formula above does not include all the digits in the results. I prefer to leave the quotation marks in...........
View 9 Replies
View Related
Aug 19, 2008
I am looking for VB code that will format a range of cells from a number format to a percentage format based on a reference cell. The reference cell is a drop down created from a list validation.
For example: If the reference cell (cell A1) =1 then cell range B2:C4 would be a number format. If A1 = 2 then cell range B2:C4 would be a percentage format.
I have searched for hours trying to find a relevant thread; I know they are out there, maybe I just wasn't typing in the correct search words.
View 5 Replies
View Related
Jan 18, 2008
I need to check a range of cells for 2 different values. If either value is present, I need to shade a cell (outside the original range) red. If neither value is present I need to shade the cell yellow. Can this be accomplished with conditional formatting?
View 2 Replies
View Related
May 16, 2009
The following code works perfect but the "change" event is only triggered when working directly on intersect range. Tried using the "calculation" event but could not figure it out. This is what I want:
1) To replace the code provided below using the calculation event
2) To only trigger the event for the row(s) where the new value was generated, not for the whole "For Each" statement
3) To use one single code for all worksheets, instead of copying the code in every working worksheet on the workbook, if feasible
4) And I would like a "second alternative", where the user of the workbook can click on a button and trigger the event on every row on the workbook that has a non empty cell within the intersect range, assuming that the intersect range column is the same for all worksheets
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean
Set d = Intersect(Range("I:I"), Target)
If d Is Nothing Then Exit Sub
For Each c In d
Select Case UCase(c)
Case "C"
fc = 1: fb = True: bc = 4
Case "O"
fc = 2: fb = True: bc = 3
Case "D"
fc = 2: fb = True: bc = 46
Case "G"
fc = 2: fb = True: bc = 5..................
View 4 Replies
View Related
Oct 24, 2013
I would like to format the color of cell A1 on Sheet 1 based on true or false values from cell range A1:A10 on sheet 2. For instance:
1. If all cells on sheet 2 in range A1:A10 were false then cell A1 on sheet 1 would be red.
2. If some cells on sheet 2 in the range A1:A10 were false and some were true then cell A1 on sheet 1 would be yellow.
3. If all cells on sheet 2 in range A1:A10 were true then cell A1 on sheet 1 would be green.
View 5 Replies
View Related
Jul 6, 2009
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 9 Replies
View Related
Apr 3, 2014
Code is trying to
1. Where there is data in column B
2. Goto first row of data
2. If column J = "Closed"
3. then select Cells from column B to J in that row and colour these cells in red
4. If column J= "Open"
5. the select Celss from column B to J in that row and colour these cells in grey
4. Repeat for next row until you get to cell in column B where there is no data.
Recieve run time error 1004: Method 'RAnge of object_GLobal failed
===============================================================
Sub Colourclosed()
Sheets("Risks").Select
Dim LastRow As Long
Dim i As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 8 To LastRow
If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select
Selection.Interior.ColorIndex = 3
[code].....
Note: I've managed to do it for one cell ie for Column B by replacing "If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select with "If Range("J" & i).Value = "Closed" Then Range("B" & i).Select" This works but only colours in cell in column B, how do i do this so it colours range of cells
View 4 Replies
View Related
Jun 12, 2008
I would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?
View 5 Replies
View Related
Jul 30, 2007
how can I add an legend entry that has been removed by error?
View 3 Replies
View Related
Jun 28, 2005
I am creating a chart where the number of data series used on the chart will change every time the code runs depending on what information people are looking for. That part works fine. I need a legend that shows which data series were added to the chart. Is there a way to get a dynamic legend on the chart that matches the data series, without manually adding it?
View 6 Replies
View Related
Dec 18, 2008
I'm running Excel 2003 and I'm getting a #REF error in the legend of a chart! It displays the #REF and then the label. Anyone any idea what causes this - I can't find anything in Help.
View 2 Replies
View Related
Sep 30, 2008
I'm working on an evaluating spreadsheet where I give for several items a rate of 1 to 3 and at the end it calculates an average rate. The average rate would range from 1 to 3 as well and their meaning is:
1 - poor
2 - average
3 - good
How can I work on the formula so when the result is equal 1, 2 or 3 it automatically gives in the cell right beside the related legend?
View 3 Replies
View Related
Mar 2, 2009
For Instance i might have a cell with the value 30KDPA
the one right below this needs to be 30KDPB
then 30KDPC
basically the last letter is going through the alphabetical order. Once it hits Z it should go to AA, AB, AC . Just like an excel file.
If i select the first cell and just drag it down it copies that exact same cell value.
View 5 Replies
View Related
Jul 2, 2006
I want to include a legend to a bubble chart in the folowing format: the smallest bubble in this legend should represent values between A and B, the second (somewhat bigger bubble) should represent values between B and C,and so on. Is it possible to produce a standard legend in a bubble chart?
View 2 Replies
View Related
Jan 15, 2007
I have a chart graphing different series, which includes a legend to label each series. The labels are linked to particular cells in the worksheet.
Most of the chart has Arial font formatting, but I need to change the formatting of a few characters in the legend:
I know that i can change the formatting of the whole legend, and even different entries in the legend. Is it possible to change the font of just a couple of characters in one legend entry?
Elements I need to change are:
Font type (eg from arial to symbol)
Size (varying from 8 to 14)
Bolding etc.
View 4 Replies
View Related
Apr 8, 2012
So i deleted a chart legend - is there a way to get this back or do you have to start all over again?
View 4 Replies
View Related
Jul 31, 2014
How do i change a title in legend on a chart. i.e. I want the label to be MGRS 2%
Job Category
Category %
EE Total
Mgrs
2%
15
Supvr
6%
51
[code]....
View 2 Replies
View Related
May 4, 2007
I have made charts in VBA charts that needs to be placed on specific position and have specific sizes on a chartsheet. I use plotarea (top, left, width, height) for that. Unfortunately when I set these values they keep chaning, there is some scaling going on in Excel and they won't get the specified size. I have been looking on the internet and finally I found a piece of code which works, but still not good enough, because the legend is also not good positioned
De data for resizing the new charts (ChtNew), the legends and the charttitle I get from other charts (ChtOrig). The legend.legendposition of ChtOrig does not have a value but it still have a top,left,height en width which I use.
I show you my code, the part with the for-next is taken from the internet.
I am trying to find a solution now for 3 days and now I only dream of huge charts (which is not good). So please can someone help me with this? This is the last forum, that I can try, I didn't get reactions from others.
with ChtNew.legend
.Top = ChtOrig.Legend.Top
.Height = ChtOrig.Legend.Height
.Left = ChtOrig.Legend.Left
.Width = ChtOrig.Legend.Width '* 1.1
.Top = ChtOrig.Legend.Top + ChtOrig.ChartTitle.Top
end with
With ChtNew.Plotarea
.Top = ChtOrig.PlotArea.Top
.Height = ChtOrig.PlotArea.Height
.Width = ChtOrig.PlotArea.Width
.Left = ChtOrig.PlotArea.Left
For i = 1 To ChtOrig.PlotArea.Top........
View 9 Replies
View Related
Jun 17, 2008
I have a 2003 vs. 2007 problem that I've spent a week or so trying to resolve, to no avail. I have created a fairly complex "dictator application" in Excel 2003. The application performs a bunch of calculations, and stores this inforamtion in a template workbook. The template itself contains one worksheet (to hold the data), and twelve empty "chart-sheets" which I later use to create plots. Of course, everything works fine and life is good in Excel 2003.
Flash forward two years, and half of my company is running 2007. When a 2007 user fires the plot creation routine, he is greated with a lovely error message (# 2147467259): "Method 'Position' of object 'Legend' failed".
Here's the full procedure I'm running to create the plots and position the legends:
Function bChartSumFileData(ByVal lTotalPlots As Long)
Const sSOURCE As String = "bChartSumFileData" 'Procedure Name (error-handling)
Dim bReturn As Boolean
Dim i As Long, j As Long, lNumSeries As Long
Dim sTemp As String, sPlotSetting As String
Dim chtTemp As Chart
Dim wksData As Worksheet, wksInfo As Worksheet
Dim serTemp As Series
Dim uSettings As CHART_SETTINGS
Dim vaPlotInfo As Variant
Obviously, lots of custom functions here; I'm also using a custom "type" to store chart settings (which I read from a *.txt file in a separate function). However, as far as the Legend goes, it shouldn't make a difference with the above code. When I stop execution after the error and look at the template workbook, the chart is created just fine, and the legend is visible, so I'm not sure why the error is occuring. Even better, the number of charts created prior to erroring-out is not fixed: sometimes the error occurs on the first plot, sometimes on the third, etc.
View 9 Replies
View Related
Nov 22, 2013
I have 10 graphs with more than 20 legend entries. However, each graph only needs 3-4 elements out of the 20 legend entries in the graph. Is there any way to force Excel to only show those legend entries that have a value? (without deleting them manually)
View 12 Replies
View Related
Dec 19, 2013
I am using bar chart to display top 5 units sold each year to my Excel file. In the data table, I have listed all the units and its number (quantity) sold. The problem is, the legend of the chart displays all the units instead of just displaying units that has values (top 5 units each year) which makes the legend hard to read (see attachment) How do I get Excel to select legends only if there is a value for it?
View 2 Replies
View Related
Dec 16, 2011
I have a chart with 20 series labels that are series 1, series 2.... etc. I want to label them with 20 corresponding cells but can't find how to do this in Excel 2010 (I seem to remember this being easy to do in older versions). At the moment I am having to click on source data and edit each label individually... Is there a quicker way?
View 2 Replies
View Related
