I want to change the fill command effekt in Excel, I want Excel to do like this automaticly when I drag the lower right corner with the cross down.
310 A1 010
310 A1 020
310 A1 030
310 B1 010
310 B2 020
310 B3 030
310 C1 010
I suspect that I can do this with the "Custom list" under "Option", but I need a way to make it more automatic. Otherwise I will write down the whole thing in there instead of making it easier.
I am trying to Change Multiple cell values using the command button. So once clicked it should change the value in the cells with the value its getting from the Inputbox. This function also changes the cells in multiple worksheets. For some reason its not working and I can't figure the problem out.
Private Sub DateChngButton1_Click()
Dim strDate As Date
Sheets("Summary").Select
strDate = InputBox("Input the date eg. april 2004", Title:="Date Change Prompt")
Range("H4").Select
ActiveCell.FormulaR1C1 = strDate
Range("I18").Select
ActiveCell.FormulaR1C1 = strDate
Range("I32").Select
ActiveCell.FormulaR1C1 = strDate
Range("I46").Select
ActiveCell.FormulaR1C1 = strDate
Sheets("Cover").Select
'strDate = InputBox("Input the date eg. april 2004", Title:="Date Change Prompt")
Range("C22").Select
ActiveCell.FormulaR1C1 = strDate
End Sub
I am having some difficulty working out how to activate the SelectionChange command if the user changes a cell in a particular column. Based on the fact that the column number may change the constant in this column will be that on row 7 the name will be "Fund Size".
Therefore my question is how do I get VBA to run my code if a user changes a cell in the "Fund Size" column...and underneath the "Fund Size" header (i.e. row 8 or greater).
I'm trying to write a code to make a number of buttons visible depending on a cell value
I have 10 command buttons all are invisible and I want to show only the first x x is the value of cell "A1" in "Sheet1" (will be from 1 to 10) Command buttons names are default names (CommandButton1, CommandButton2, ... , CommandButton10)
Note: I'm working with a worksheet not a userform
I have a command button that when pressed I would like the name written on it and colour to change
View 2 Replies View RelatedI am trying to create a program to automate the gauging figures on oil barges, currently everything is done by hand and takes approximately 20 minutes to complete. I have the charts entered for the tanks already and have the code set so that when you click on a tank "gauge" it will enter the "volume" which corresponds to that gauge on a totals sheet. Here is where my problem is coming in
The barges are gauged at four points
"before loading"
"after loading"
"before discharge"
"after discharge"
I want to set a worksheet as the default page with four command buttons that let the user select which operation he wants to perform. before load figures. after load figures and depending on which operation they select have it enter the volumes in the appropriate cells on the totals sheet. So if someone selects "loadopen" command button I want the following code to run on my worksheets............................
I have this code for 10 Command Buttons to change the font colour. This is part of a much larger piece of code, but to simplify it, I am just using this
Sub ForeColour()
Sheet1.Cat1.ForeColor = &HC0&
Sheet1.Cat2.ForeColor = &HFF0000
Sheet1.Cat3.ForeColor = &HFF0000
Sheet1.Cat4.ForeColor = &HFF0000
Sheet1.Cat5.ForeColor = &HFF0000
Sheet1.Cat6.ForeColor = &HFF0000
Sheet1.Cat7.ForeColor = &HFF0000
Sheet1.Cat8.ForeColor = &HFF0000
Sheet1.Cat9.ForeColor = &HFF0000
Sheet1.Cat10.ForeColor = &HFF0000
End Sub
In Excel 2003, this code works instantaneously, but I am trying to run the same code in Excel 2000 and it takes 10 seconds. Is there any way that I can speed this up in Excel 2000?
The original file was created in Excel 2003, but I have created a new file in Excel 2000 with the 10 buttons on it and the code and it still takes 10 seconds.
I am looking for a way to alter the appearance of a button when a second one is clicked. I have two buttons, "Yes" and "No", and when one is clicked I would like to 'grey' the font in the other without disabling it, and vice versa. I have tried recording a macro as I go into the button's properties and change the foreground font, but it does not record the font change, only the selection of the button object.
View 2 Replies View Relatedhow to change in a for next loop the CommandButton.caption in a usersform
For n=1 To 300
CommandButton & n.caption= Sheets("info"). cells(n,1)
Next n
i want the user to be able to change the cell color on the click of a command button. the worksheet is protected. when the user click the command button the active cell changes to red and offsets by 1. then the work sheet is locked again.
The two problems I am having is 1. I want the range to begin from row 10, column k to column FD. all cells down
the second problem is the current code allows me to edit locked cell columns A to J ...
I want to input a column of formulas where the part of the formula which changes on each row from the row above is the worksheet name. I want the cell ref to remain the same
ie
formula in B6
='WC - 23-03-09'!J2
formula in B7
='WC - 30-03-09'!J2
The sequence of the worksheets should follow the sequence as they appear left to right in the worksheet bar at the bottom of the page.
I was looking for a way to "fill" in the rest of the column where its the worksheet which changes sequentially in the formula rather than the cell ref
Auto fill downwards until row value change
View 3 Replies View RelatedI'm looking for some code to change a cell's fill colour
eg. by selecting an individual cell in a range, say A1, the colour of a cell, say K1, in a corresponding range changes to yellow
or if I select multiple cells, say A1:A9, the colour of cells K1:K9 change to yellow
When doing a normal copy and paste of a formula you get 1 cell added to the formula
ie... a1=b1+c1
when dragged and copied to a2 you get a2=b2+c2
Is there a way to make it add more than one cell so that when the first formula is dragged and copied I would get a2=b17+c17
The actual formula I am doing a copy and paste with is:
=IFERROR(CHOOSE($I$2,'YTD & MTD'!D58,'YTD & MTD'!E58,'YTD & MTD'!F58,'YTD & MTD'!G58,'YTD & MTD'!H58,'YTD & MTD'!I58,'YTD & MTD'!J58,'YTD & MTD'!K58,'YTD & MTD'!L58,'YTD & MTD'!M58,'YTD & MTD'!N58,'YTD & MTD'!O58,'YTD & MTD'!C58),"")
I am needing to copy and paste to the next row but want 'YTD & MTD'D58 to change to 'YTD & MTD'74
Function BG(InRange As Range)
Range("InRange").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Function
That so far but not quite sure why that isnt working. I want to change the fill color with a UDF that all they do is select a Range and it changes those fill colors to whatever the Colorindex may be. I didnt find anything while searching the forums with this already.
I have a code i want to fill down a column, but have the row change to the corresponding row it's on
So for example, the code is '= code128(A7)' on row 7. How to fill it down, but they ALL say (A7) on row 8, i need to change it to '(A8)' and next '(A9)' and so on.
How can I change the fill in the ChangingCell to, say, ColorIndex = 15, if the goal seek method is successful
Currently I've in a loop:
Cells(myrow, colSet).GoalSeek Goal:=Cells(myrow, colVal), _
ChangingCell:=Cells(myrow2, colChange)
I have a spread sheet with various tick boxes, that when ticked calculate an accumulative percentage in a cell. This cell is the basis of my graph. e.g. if the cell displays 80% - the chart with show 80% - simple.
however, I want to write a vba code that changes the fill colour of the chart depending on the percentage.
i.e. if the percentage data = 0-49% I wish the chart to display as red. 50-69% - yellow and 70%+ = green.
2 Questions here...(Entire macro is at the bottom)
QUESTION 1 - In line 4 of the code below, in Column B, how can I get this code to auto-fill ONLY equal to the bottom-most row with any data in column A?
Note that it's never exactly "B254"
QUESTION 2 - How can I change these 3 lines of code...
I have a series of dates that I need filled down the spreadsheet, however the dates change at different intervals.
The best way I can describe it would be;
IF: Cell contains text
THEN: Copy Text
ELSE: Paste Text
Rinse and repeat!
My spreadsheet looks something like: ..
I'm using Excel 2003- and I am trying to change the fillColor of a cell based on the value of that cell.
11111122222222233333333344455555555555556666777888
If I use Conditional Formatting I can only use 3 colors, I'd like to use 8 or 9. Is there another way to do this without using the Conditional Formatting?
I'd like the result to look something like this-
11111122222222233333333344455555555555556666777888
I've looked at the similar threads in this Forum, but I couldn't find exactly what I needed.
I am busy with a project using VBA to populate a spreadsheet based upon the data entered into a particular cell. The problem that I am experiencing is that I populate the initial cell via a combo-box which I cannot exit unless I click elsewhere with my mouse. The code that I am using to populate the other cells is as follows
Private Sub Worksheet_Change(ByVal target As Range)
Application. ScreenUpdating = False
On Error Goto dump
Dim r As Range, MyRowNum As Long, ws1 As Worksheet, ws2 As Worksheet, strPriCode As Variant, _
strNAPPIE As Long, strSInCost As Currency, strSInDesc As String
Set ws1 = Worksheets("SINVOICE")
Set ws2 = Worksheets("STOCKIMP")
Set r = ws1.Range("B11:B35")
If Intersect(target, r) Is Nothing Then Exit Sub
If target > 0 Then....................
I have set up a rollover hyperlink so that when I hover over the cell it changes colour and creates a good effect. To do this I have another cell on the sheet that turns to 1 when the cell in question is hovered over.
I need a code so that when that cell displays the number 1. the object (which is rectangle 2) changes from white fill. to a picture I have saved on my desktop.
I cant quite figure out the coding and have been messing about with things like..
if cell range e.g a1 = 1 then rectangle 2 .userpicture " path " else .solid etc.
I'm recording a macro, so a line in my graph is a specific green. The marker fill won't retain the green, it always ends up blue. the lines and marker line is green. no matter what color I try the marker fill will always be blue. How to correct this?
View 6 Replies View RelatedI am aware of the ColorIndex, but more specifically I need to change the fill color of a cell that is 9 columns to the right of the active cell. I can't find out how to make this work. to clarify:
ActiveCell + 9 columns.Interior.ColorIndex = Yellow
There has got to be an easy way to do this.
As seen from my attached file,
The colour of the percentage cell changes according to its percentage. I would like the S/N cells to have the same colour as the total percentage cells automatically. (Even when the percentage updates)
Secondly, for the cells under "Target", if the target dates are 1 day before today(the current date on a particular day) and the actual date is not filled, the cell fill will turn amber.
If today is on or after the target date and the actual date is not filled, the cell will turn red.
However, if the actual date is filled, the target date cell will be filled green, overwriting the above two condition.
FormattingHelpExample.xls
Let's say I have column C blank, but every time it's filled in with something I want column A to be today's date and B to be current time. Unfortunately, the functions NOW() and TODAY() don't give me what I want. They get recalculated every time that I update the spreadsheet.
Another thing that I can do is just press ctrl+shift+; to generate a non changing date/time, but I want it to happen automatically (being super lazy).
i want to fill down a column and instead of my formula changing from A6 to A7 i want it to change to B6.
View 9 Replies View RelatedI have a simple spreadsheet. A column for a persons name and 31 columns, one for each day of the month. I want to apply conditional formatting, either fill color or border color, to the date column of the current date when the spreadsheet is opened.
View 2 Replies View RelatedUsing Excel 2010. Is it possible to change the default line thickness and fill color when selecting the ActiveSheet Target Row below?
I would like a thin border and a light grey fill - without interfering with any fomatting or conditional formatting that has been applied to the worksheet.