I am looking for a vba where by the default colour of the font for new text typed in any cell of a workbook change to green or red and the default black automatic becomes secondary. The existing text in the workbook in its existing font colour must not change i.e stays in black or red.
View 1 Replies
I have Excel 2002. If I start the program it opens with a blank workbook, called "Book 1".
If I then open an existing workbook, I have two Excel buttons in the Windows taskbar: the one I just opened, and the blank one titled Book 1.
At work I have Excel 2003. The blank "Book 1" is also there when I start the program, but if I then open an existing workbook, the Book 1 goes away. I like this way better.
My question is: Is this just the way versions 2002 vs. 2003 work, or can I change a setting somewhere to make Excel 2002 close its default blank workbook when I open an existing one?
I am trying to get the brown boxes on the master page to change colour brown for occupied and green for vacant. I would like this to happen when I change the tennancy type in each tab B1,B2,B3 ect. I was trying to use name ranges to get this to work, But I seem to have stuffed it up.
View 5 Replies View RelatedIs there any way to change the border color of a cell by running a subroutine? Here is my example:
Col 1
Col 2
1
2
3
4
I have an existing table with data that is updated daily ... more rows are added. Currently, the cell border is black. I would like to run a macro such that the column header is shaded grey and the borders turn into a shade of gray. I am interested in the 35% grey.
I have five columns D3 to H3 all with different amounts/currency value. I'm looking to get a formula or use conditional formatting to scan those five cells and highlight one of them green when Excel has determined which one is the cheapest out of the five.
View 14 Replies View RelatedI have a spreadsheet with sales which I colour green when they are outstanding and red when they are despatched. I would like to automatically add a 1 in an adjoining column when the cell is green (eg outstanding) or zero when they are despatched so that it totals up the number of outstanding orders.
a b
172.95 (this column would be green)
024.00 (this cell would be red)
124.00 (this cell would be green)
132.25 (this cell would be green)
I have already got an anwer for this long back from this site. The code was writted by Mr. Krishnakumar
the thread is here :[url]
i need some changes to be made in this code. The existing code creates and updates the details in the sheets automatically from the master data. I just need the sum of Column I in all the sheets after the last row of Column I.
selecting all the sheets and typing the formula in I column is not possible because, the last row in Column I is different in all the sheets.
In sheet 1, the last row of Column I is Row 15, in sheet 2 Row150 is the last row.
I guess something could be done in macros.
follwing is the existing
Sub TestIt()
Dim sWS As Worksheet
Dim Sellers As Range, Seller As Range
Dim lRow As Long, fRow As Integer
Dim CopyRng As Range, ws As Worksheet
I have a table with a range A1:C6
If B1 is “hello” I want B1:B6 background to change to green, and text to stay black
But if B3 is “goodbye” I want this to override the above and make B1:B6 background white and text colour of B1:B2 and B4:B6 white (so the text in these cells seems to disappear).
I have been trying to get Excel (2007) to change the colour of tabs to match other tabs in the workbook
Using this code I get the colour code of the current tab
Code:
x = ActiveSheet.Tab.ColorIndex
But when I use this value in a procedure like this:
Code:
ActiveSheet.Tab.ColorIndex = x
The tab is a different colour!
I would like the following to occur in an excel spreadhsheet:
If in Sheet1 Cell M98 is manually highlighted in Yellow (Not conditional format) then on sheet 2 cell range M84:M76 will also be highlighted in the same colour.
Cell M98 colour will only either be blank (no fill) or Yellow. If M98 is blank then the cell range M84:M76 would also be blank.
Excel 2002
I have a simple spreadsheet and am trying to get some Conditional Formatting to work.
Cell1 = Name
Cell2 = 1
Cell3 = 2
Cell4 = 3
If cells 2, 3, and 4 all match I want the background in cell 1 to change to green.
I've tried (Formula Is =B1=C1=D1 green) with no luck.
Copy data from workbook, open existing workbook, select range and paste. But my copied data is lost.
Sub Select_Copy_Paste()
'
'
Windows("ElektroFunctiesDatabase.xlsm").Activate
Sheets("PowerSupply's").Select
Range("A2:I6").Select
Selection.Copy
[Code] .........
' Here i need to do something to paste data into r.address?
Here's my macro:
Code:
Sub CopyRow()'
'Copies row to new sheet, highlights it, marks column 'A' as copied.
'
Dim cCell As Range
Set cCell = Selection.Cells(1, 1)
Selection.Copy
Sheets("Sheet2").Select
Rows("2:2").Select
[Code] .....
Is it possible to modify it to paste into a different workbook called c:filesDestination.xlsm, instead of the existing workbook (Source.xlsm)? The destination sheet name is the same (Sheet2). It's OK if both workbooks are open at the same time.
I'm trying to make my Excel/VBA application look as little like Excel as I can and there's now almost no trace of Excel - so is there any way I can replace the standard Excel green "X" icon that appears on Windows Taskbar with my own?
View 6 Replies View RelatedThe amazing code I saw was from this thread Auto lock cells after data entry when file saved... where the code was made by Jafaar Tribak.
It basically locks cells that have had data inputted into them when the file is saved (with a nice prompt warning of the used cells being locked.
This is Jafaar's file [URL]
And this is my file where I'm trying to make it work in the "Employee data entry" Sheet:
[URL]
And finally this is the file after I tried to copy the code over : [URL]
Trying to make it so that in the employee data entry sheet locks cells after an employee inputs some data into said cells.
I thought that by setting an "inputrange" on the employee data entry sheet the code would track it down automatically but maybe I'm completely wrong. I cant see anything wrong with it (probably because I"m not that good at vba) but I fear I may have left out something really simple!
I seem to disable my other macros when i try to insert this code in (I have a macro that when you click on a cell a calendar pops up so you can click on a day from the calendar rather than typing out the date)
i came up with this..
Sub Copy_To_New_Workbook()
Application. ScreenUpdating = False
Dim strFileName As String
strFileName = Application. GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", Title:="Please select the excel file to be copied")
I have a very brief question
dlgAnswer = Application.Dialogs(xlDialogOpen).Show
Selection.Copy
Windows("Derivative YK pricing Mod G.xls").Activate
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("C5").Select
Application.CutCopyMode = False
Windows("EXPORT1.xls").Activate
ActiveWindow.Close
As you can see, I open a workbook with the dlgAnswer, I was wondering how I could close that opened workbook
Currently I have
Windows("EXPORT1.xls").Activate
ActiveWindow.Close
however the workbook will not always be called export1.xls, I want it to close whichever one I opened earlier.
How to Automatic open others Workbook when we opened the Master Workbook.
Examp.: I have a workbook (Master.xls), and when this file be opened, and then others workbook (Transactions.xls, Order.xls, etc.) is opened too.
inability to set a default row height and still allow the occasional row that requires more space (i.e., one of it's cells has multiple lines of wrapped text) to AutoFit if necessary.
Say you have the data shown in NormalSettings.png in an Excel file (see attachments) and you want to have a little more room between the shorter rows, but not have them all set as big as the expanded rows. One way to do this in bulk is to select the entire spreadsheet and set a fixed row height, but this chops off any cells that require more space than your default height (see FixedRowHeight20.png). The only way to fix this is to manually scan through your sheet and individually select all rows with cells that need more room than your preferred row height and AutoFit them (good luck if your spreadsheet is any size...) . If you try selecting the entire sheet and choose Format-Row-Autofit, you're back to the crowded display shown in 'NormalSettings.png'.
My workaround to this annoying problem is this: choose a column that you're not using (I just select the very last column in the sheet by holding down CTRL and pressing the right arrow until the screen stops moving) and highlight the entire column by selecting the column header. Then increase the font size for that column...voila, even your blank rows will now AutoFit to the new font size rather than the font size you are using for your data...effectively increasing your default row height without sacrificing AutoFit capabilities. I find that using 16 as a dummy font size makes my data (which is font size 10, Arial) look nicely spaced out, but experiment and try stuff until you find what you like!
how do i change the default settings in excel, for example when i open excel it shows numbers on both rows and columns and i want it to show letters on the colunms. i know how to change the r1c1 reference style but how do i get it to stay the way i want?
View 4 Replies View RelatedI have got a buttion that changes various layout and borders for the page then shows a printpreview, after printprewiew closes.
Then a msgbox that askes if you would like print. Is there a way to change the defult printer between local printer and adobe distiller(.pdf) as a dropdown option box. Or Yes for Local printer and NO for adobe distiller(.pdf) using vbYesNoCancle.
When I open Visual Basic Editor and insert a module,the default name
is "Module1",How can I change the default as "Mymod1" or others ,and if
I insert another module later,it can be named as "Mymod2" by default?
I have searched the registry and found nothing about the item.
I am setting the fill color of a cell by setting the .Interior.Color property and using the RGB() VBA function. My VBA code saves the spreadsheet in Excel2003 format at the end of the creation process. This has worked great and my spreadsheets looked correct when reopened in Excel 2007. When these same spreadsheet files are opened in Excel 2003 the colors are all very different. Does Excel 2003 handle colors differently than Excel 2007? Is there a preferred way to specify colors that will work on both platforms?
I have attached a specific example. The color is #B4CF27 or RGB(180, 207, 39). When I open this sheet in Excel 2007 the color is a shade of green (the desired color). When I open it on Excel 2003 I get yellow. I tried swapping the red and blue values thinking there might be some byte ordering issue and it does not produce the yellow on Excel 2007.
I need to default size to be bigger. Is there a way to do this so I do not need to re-size the box every time?
View 2 Replies View RelatedI want to set the default row height as 20.0 (not excel's standard 15.0). When I delete the contents in a cell that has Wrap Text property enabled, the row always reverts back to default height of 15.0 and this is too small. Is there a way to chnage the default?
View 6 Replies View RelatedWhenever updating an Excel file and saving it as a new file, a "Confirm Save As" window appears. The default is "NO" (No is Highlighted). Conversely, in MS Word, the default is "Yes" (and, YES is highlighted). I would like to change the default from NO to YES in Excel.
View 1 Replies View RelatedI would like to change the default color of any excel sheet I use to grey from the standard white and I am struggling to find out how this is achieved.
In other words, I am trying to have all cells in the sheets I work with appear grey on my screen by default. A coworker of mine used to work in banking and she has grey cells but does not know how she changed them.
When I use the GetOpenFilename() method, it seems to default to the particular users My Documents. Is there a way to force it to default elsewhere like a share drive, or the users desktop?
In the same line of thought, if I have a file named TestFile.xls. and I want to try to open it from the users desktop if they have it, how can I do this, since their desktop location is different than mine?
I'm trying to change the default colour palette for "fill colour" and "font colour". The way I've been doing it before, is to have a spreadsheet with the colour palette I want in my XLSTART folder, and then when I open a new document, going to tools->options->color->copy colors from, and selecting that spreadsheet.
However, this is a pain, and I was wondering if I can just set the colour palette to have my colour scheme already there, whenever I create a new spreadsheet.
i am trying to change the date format for the B column.
if you look the format for this column is dd/mm/yyyy, BUT, it is not. the real format should be mm/dd/yyyy.
ex. 08/01/2006 (mm/dd/yyyy) but excel thinks it is dd/mm/yyyy
