Just trying to work out the best way to be able to determine autoshape colours using cell values. For example I have 12 shapes that need to coloured either Red, Green or Orange depending on a specific cells value, 1 for Red, 2 for Green and 3 for Orange.
I have sort of managed to successfully complete this for 1 of the shapes, by using the RGB Fill option (Using 3 = Red, 4 = Green and 45 = Orange). But cannot for the life of me work out how to do this for multiple shapes using different cell triggers. Below is the code that I currently have and the spreadsheet that is linked to:
Got a workbook with two sheets. Under Sheet1: I create two Autoshape Bevel's. One is to display the two pictures, one is to hide the two pictures. If I hit a Bevel 1 or Bevel 2, I want the code to be written into the Workbook so when I open it up again it will remember what settings I had.
Under Sheet2: In the VBA code I have. I want to put a Two IF statments in here to make the logical decisions for me. I have tried it but they can't recognize the AutoShapes. I was using "ActiveSheet.AutoShape("Autoshape##")_Click", but it was not picking up a variable.
Private Sub Worksheet_Activate() ActiveSheet.Shapes("Picture 32").Visible = True ActiveSheet.Shapes("Picture 33").Visible = True ActiveSheet.Shapes("Picture 32").Visible = False ActiveSheet.Shapes("Picture 33").Visible = False End Sub
I link the two bevel's on Sheet1 to run the Macro on Sheet2!Worksheet_Activate. How do I find the name of the Bevel's? Can I use If Statements, so make decisions on which Bevel was pressed, and retain the settings for the next time I open the Workbook ?
I have a chart which has Autoshapes linked to other cells in the spreadsheet above each bar on the chart. Is there any way of making them dynamic, so that if the value of the bar changes, the Autoshape will move up or down with that bar?
I've got a workbook for aesthetic reasons having text entered by users directly into protected autoshapes. I wanted to link text in one autoshape with another. If that was not enough I wanted to carry forward that text to another autoshape if a different autoshape did not contain text!
I have a line graph which I have annotated using the autoshapes function in Excel. Various high points and low points have notes attached in order to make the graph clearer to clients.
Unfotunately, if I want to update the range of the graph (say to include two months instead of the usual one,) none of the autoshapes move.
I thought that I could use Excel... All I want to do is to use cell data to inform an autoshape, e.g. the length and width of a rectangle, etc. without resorting to VBA.
If cell A2 has the number 5 in it (as a result of a calculation in that cell), is it possible to get the next five cells in that cell to colour themselves in?
Obviously, as and when the value in cell A2 changes, i need the number of cells coloured in to change as well.
In addition to this, the rows that this will happen in will vary week to week so i'm not sure if VBA is the way to do this.
Also would there be any problem with negative figures as when that happens i don't want any cells to colour in at all.
I have a spreadsheet that contains data I import from various sources. Once the import is complete I need to colour various cells based on their contents. Normally I'd use conditional formatting, but there are more than 3 conditions so I need to use VBA (preferrably that i can turn into a macro and assign to a button to click once I've finished the imports each time)
eg: Everytime a cell content is the word "RED" , colour it red - then the same with various other colours.
Google has found me a change event piece of code but it doesnt work when you're cutting and pasting some info.
I have a rather large database that I'm trying to automate colours in, based on data that is inserted into Column 'D'. I've used the following code (with some success), but it colours columns that I don't want to colour:
VB: Sub KeyCellsChanged() Dim Cell As Object For Each Cell In Range("D1:D5000")
[Code].....
That works fine - but the problem I have is that I only want to colour columns A:N, Q, T, V, AB:AE, etc. (random columns and not always together). I have already tried to replace parts (as follows), but get all sorts of errors (in particular Run-time error 1004):
VB: If Cell = "Rabbit" Then Cell.Range("A:N,Q, T, V, AB:AE").Interior.ColorIndex = 42
Is there someway that a line of code can determin which cells to colour in the row, or alternatively code that will colour the columns a specific colour and make them stay that colour when the first code above is used?
How do i have colouring to a reference cell what i mean is that on certain formula i get BUY or Sell on that cell. As per the conditional formatting i have made the colour change BLUE FOR "BUY" & RED FOR "SELL"
On second sheet i link the above TEXT., but the COLOUR OF THE TEXT Does not come.
Does CONDITIONAL Formatting come with reference cell.
how I might be able to colour certain cells in a row a specific colour when a specific value is input.
For instance: The value "A" is put into row A1. A1 and C1 cell colours change to green The value "B" is put into row A2. A2 and C2 cell colours change to orange etc. (not sure how many colours yet)
I sort of have a script set up, but there are certain things I dont know how to do. Like target the specific cells that need colouring.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 0 Then Exit Sub Select Case Target.Value Case "A" Target.Interior.ColorIndex = 1 Case "B" Target.Interior.ColorIndex = 2 Case "C" Target.Interior.ColorIndex = 3 Case Else Target.Interior.ColorIndex = 0 End Select End Sub
I have a gone through quite a few topics in this forum.....but couldnt find answer to my question....may be because its too basic. I have a worksheet with 10 columns. I am trying fill colour in the rows that have "text" in column J (in other words.....their column J is not blank). I figured out how to fill the colour in the cells in column J when they are empty, but cant figure out how to colour the entire row from A:J.
I'm trying to automatically shade a number of cells dependant on the value in a single cell. ie. If D2="quote" then I want A2:Q2 to be yellow. If D2="Design" then I want A2:Q2 to be green etc etc. I have about 6 options in all of what D2 could be. I then want to repeat this for about 200 rows.
I've been trying to get a macro together which would colour all the tabs in my workbook based on whether or not a certain value exists in a certain cell. In this case the cell is C7 and the word there is "elective class:".
I have a column for each month where the dates are entered on rows. Is it possible I can color a cell if the date entered in a cell for that month is 15 days past the end of the month. Like, if in a January column I enter a date on one of the rows as Feb.20, then the cell fills with red colour. I tried doing it using conditional formatting, but don't know how to say 15 days past end of the month?
I am currently using a piece of code (within a bigger piece) that colours the dates in two columns based on how close it is to the current date. It works fine but sometimes the sheet it is looking in has over 3500 rows so it is taking for ever (well over 6 mins)
The code is
HTML Code:
Sub ColDate() 'date case opened application.ScreenUpdating = False Dim MyRg As Range Dim F As Range Dim DateDiff As Long Set MyRg = Range("g1:h" & Range("H" & Rows.Count).End(xlUp).Row) For Each F In MyRg
I currently have some code which reads a stock file, adding up the stock for each individual item (which may be in one or more locations in the warehouse) and then placing the total stock count in a cell with the detail (of how much stock is in each location, the location and the expiry date) and a separate line for each location, in a comment.
The comment is constructed one line at a time as the code finds the stock records, see code below:
With shtWorking.Range(strStartColumn & intRow + 2) oldComment = .Comment.Text .Comment.Delete .AddComment oldComment & vbNewLine & strComment .Comment.Shape.TextFrame.AutoSize = True End With All this works very nicely, but I want to colour individual lines depending on the expiry date. This can be done manually in Excel, but I cannot find a way of colouring individual lines of text in a comment using VBA.
So, does anyone know of a way I can colour individual lines of text in a comment, using VBA?
As you can see from below I already have a spreadsheet which updates the fill colour of cells based on a certain criteria. This was initially set up in Excel 2003....I have now moved onto 2010 and want to use a certain colour based on it's RGB value. I tried as you can see from below, the part which is commented out (as it didn't work)
Code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim n As Long, lngIndex As Long For n = 6 To 842
I'm a teacher and would like to show progress in my pupils achievements by colouring coding cells in excel. For each subject they are given a level, e.g. 2b. If they show progress, they would move up, i.e. to a 2a. The whole progress scale is shown below:
To make the spread sheet visual i would like colour code the cells depending on whether progress has or has not been made. If they go up (e.g. 2b to 2a) i would like the cell to turn green, if they go down (e.g. 2c to 1a) i would like the cell to turn red. If not progress has been made then the cell can stay uncoloured. Each cells colour would only be dependent on the cell directly to the left.
I am trying to get a row of cells to highlight a percentage based on a date range
Below is an example of what my spreadsheet will look like, very simple for managers to read and understand but I am stuck on how i can get this to display the right way.
In the example i would need the Jan column to colour for a certain percent for 21 days and continue to feb for 26 days. Im not sure if this makes sense but this is what they are asking for. Colour bars to simple show the percent of days off each month.
Name Start Date End Date Jan Feb Mar
Dale 11/01/14 26/02/14 21 days 26 days
I have attached the spreadsheet for an example : Book1.xlsx
I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).
The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.
If the file is opened with macros disabled then only one sheet will be visible. If the file is opened with macros enabled other sheets are visible.
The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)
The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.
I'm using a CALL Macro to split up a HUGE macro into different pieces:
Code: Sub RSLDASHBOARDV2() 'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D. 'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D. 'Do Not Modify Code Unless Given Proper Privileges to do so. Dim APPSPD As Worksheet With Application .ScreenUpdating = False .Calculation = xlCalculationManual
[code]...
The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:
Code: objField2.PivotItems( _ "TRC").Position = 1 objField.PivotItems( _ "MEDCO MAIL OR AOB").Position = 2
When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.
Code: Sub STATSPIVOT() 'STATS PAGE BASED ON STATS DATA TAB Sheets("STATS DATA").Select Dim objTable As PivotTable, objField As PivotField ActiveWorkbook.Sheets("STATS DATA").Select Range("A1").Select
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
I have a macro where I manually select a cell then the macro kicks in to copy and paste the contents into the Find function. From there it goes to another work sheet, clicks on a cell in column one and searches for matching cell contents. Then If false it manually goes back to the original worksheet/cell and then I input a N in the left adjacent cell. If true I enter a Y. Right now I can only run this for the specific cell the macro was recorded for. I would like to expand this.
What I would like to do create a loop in the macro that waits for my cell choice input then continues with the with the Find function. If false the macro should just loop back to the original worksheet/cell and wait for input, ie the next cell selected. However, if true a worksheet/cell is selected, a Y is input and it loops back to the original cell and waits for input.
This is the code I use to call a macro when the macro Im calling is in the same workbook.
[Code].....
However, I would like to call this same macro when using another workbook. I copied the macro "Clearformating" and pasted into a personal macro workbook module. However when I add this code to the sheet tab it will not run the macro.
I have a macro that I found somewhere on the net to look within a folder and list all the files of a certain file extension.
The macro to do this is in the attached example and is called 'Get_File_Names_Within_Dir_ext'.
I have created a basic userform outline, 'UF1' for the user to define: Select File Extension Select Folder to Search Destination Sheet
I just don't have any idea how to sync the two.
If you type 'exe' into 'TB1_File_Extension' of 'UF1' the macro should search for '*.exe' files within the specified folder.
The search folder 'RefEdit1' box should open a windows explorer box (or some such) so that the user can select the directory in which to search for the previously specified file extension.
'TB2_Destination_Sheet' is a text box for the user to type the sheet within the workbook in which to list the files found within the specified directory.
'CB1_Find_Files' should activate the macro to find any files for the specified criteria.
There is also a Button 'Find File Types' in Sheet1 of the file which should activate the userform 'UF1'.
