When I return to a sheet by clicking on the tab of the sheet, the sheet is not immediately visible. When the sheet does become visible a cell has a black flashing background. Sometimes, but not always, the flashing cell is the activecell. Through the activate event of the sheet I attempt to make $A$1 the active cell:
View 7 Replies
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 need to add a background picture to a spreadsheet that only shows on the print area.
This spreadsheet is being used as a pro forma invoice that can be emailed to customers for them to view & print as required.
At the moment I can add a background image but this appears throughout the sheet and not in the place I need it to i.e. the print area.
I originally posted this question with out the code I'm using. I am trying to make a specific cell flash to catch the attention of other dispatchers. The problem is when I we try to close the active page it will automatically open. The only way we can completely close the page is to close Excel completely. Doing this though also closes several other pages that we need to have remaining open.
Public NextFlash As Double
Public Const FR As String = "B3"
Sub StartFlashing()
If Range(FR).Interior.ColorIndex = 3 Then
Range(FR).Interior.ColorIndex = x1ColorIndexNone
Else
Range(FR).Interior.ColorIndex = 3
End If
NextFlash = Now + TimeSerial(0, 0, 1)
Application.OnTime NextFlash, "StartFlashing", , True......................
This might sound daft, but im just wondering if it is possible to Flash text 2 different colours in a cell. Cell A1 will say - - - Well done. and I want it to flash blue and red, but not use VBA to do it. Can it be done or am I wishful thinking? This would be on Office 97.
View 6 Replies View RelatedI mean to format a cell in a way that it will flash if certain conditions are met. For example : to make C14 to flash if B14>0. Different colour formatting is not enough to catch the "oldies" eyes!
View 4 Replies View RelatedI need to get a range of cells to flash when they get close to todays date, I have tried but can't do it, not very good at conditional format or vba, easy step by step if poss, i have sheet if required,
todays date = 11/01/12 (in cell O1 as autodate)
title in cell K7 is date on site
in Cell K8 = 13/01/12
in Cell K9 = 19/02/12 etc....
I would like this cells background to flash if its 2 days from the onsite date
I have % figures in the following cells, E20, G20, I20, K20, L20, F24, H24, J24, G28, I28, H32, F16, J16, G12, I12, H8.
These figures are the result of a formula of other cells, with constantly changing values, therefore the % figures are constantly changing as well.
What I would like to do is highlight the cell with highest % figure, ideally to 'flash', either cell colour or flashing text.
I currently have this code for counting commas in each cell in range giving me the total number of different points in the entire row. Here's the link to the original question as well.
Code:
Sub Totals()
Dim X As Long, Count As Variant
Sheets("App Communication").Select
For X = 3 To 59
Count = UBound(Split(Application.Trim(Replace(Replace(Application.Trim(Join(Application. _
Index(Cells(X, "E").Resize(, 57).Value, 1, 0), ",")), " ", "X"), ",", " ")))) + 1
Cells(X, "BJ").Value = Left(Count, CLng(Count))
Next
End Sub
Now I am looking for a way to count the number of points that have been turned blue. Some times the text in the whole cell is turned blue, others just one or two of the points is turned blue.
I found this formula and it works great, but I need to count only items with black font. We use red font to identify lost items so don't want to count them as part of our inventory.
View 14 Replies View RelatedAt the moment I have two macros. One loads up a background picture into my work sheet
when I start it up, the other two are called ‘StartBlinking’ and
‘StopBlinking’.
This is the macro code I have to load up the picture:
Sheets("CASHFLOW + FUNDSFLOW").SetBackgroundPicture Filename:= _
"G:EH_Background.JPG"
What I hope to achieve is:
1. Get the picture to ONLY load up if it actually exists on the G:
drive, otherwise do nothing.
2. If value 1 to 13 is input in Cell C3, run the macro
StopBlinking.
3. If Cell C3 is left blank again, run the macro StartBlinking.
When I try to copy formulas across do I get a little black plus-sign in the lower right corner of the cell, when I point to it. I can then drag the plus-sign to the the area where I want to copy the formula. This plus-sign has now disappeared.
View 3 Replies View RelatedIn a range of cells (A1..d8) which have background set with multiple colors. Is it possible to set another range of cells (H1..K8) whith the same backgrounds as the other range and so that if one cells background changes the cell in the second range whill also change to that new color?
View 11 Replies View RelatedI have a userform where I have 2 comboboxes. The first combobox shows the the first column (only 1 of each) and the second comboBox shows me the secondary list that correlates to the valuse in the first from column B. Now I have a text box that I am trying to get the value from column C depending on what I have in the first 2 comboboxes. What is the easiest way to do it? This is all in VB since it is a UserForm, and using Vlookup seems to be too many lines if I go that route. Is there a way to use Index and Match in VB where it would be more efficient? I attached just a sample of how the data would be layed out in the Excel sheet.
View 4 Replies View RelatedI want a macro to show a message with a content in the cell B3.
For EG: "Rec as of 'B3' is created"
This has to be the message and 'B3' has to be the content in the cell B3.
I tried doing it but i am unable to show the content in B3 in the message.
I have a Textbox on a Userform that allows users to enter text and code copies the text to a nominated cell on a sheet. My difficulty is that when the text is copied to the cell at the end of each line of text there is a small 'open square' symbol that I would prefer not to show. I can manually delete the symbol but would like it either not to appear of be able to automatically delete it. If I copy the text to a word file the symbols do not appear.
Private Sub CommandButton1_Click()
Sheets("Marketing").Range("b4") = UserForm1.TextBox1
Me.Hide
End Sub
How to make a text background on editable cells and print without text background?
View 4 Replies View RelatedI am getting 0 or 12:00 AM when I format cell as time and put formula.
I working on timesheet using this formula (=SUMIF('2'!G4,"="&TODAY(),'2'!E17)). If date in G4 on sheet2 match with todays/current date then copy data in cell E17 to sheet3(b11).
G4 = todays date
E17 = time eg. 2:25 AM ( I have to format destination cell as time because E17 has time value
Formula works fine but when there is no data in E17 or E17 is blank then my destination cell shows 12:00 AM.
I have the following code, that I found on this forum, in my worksheet change event.
Private Sub Worksheet_Change(ByVal Target As Range)
Static old_value As Variant
If Sheet1.Range("C5").Value <> old_value Then
'a change has occured in cell C5 so do your processing....
MsgBox "Changing 5"
old_value = Sheet1.Range("C5")
End If.............................
Why is it that when I edit some cell's formulas and press enter the result is not the changed formula but the formula itself complete with the '=' sign infront of the fuormula. The work around for me is to cut the formula and paste it into a new cell then drag the old cell over the previous one I tried to edit.
View 3 Replies View RelatedI am currently using this Formula to Search a table on sheet 2 (Tabelle2) and copy the date in the relevent cell (Cells) into the correct cell on Sheet 1 (Master Sheet).
[Code] ..........
Used in cells E10:AZ30 and E33:AZ46
This works perfectly and is the formula behind all relevent cells in Sheet 1.
I would like to keep this but to add that it also copies the background color of the cell in Sheet 2 to the cell in sheet 1.
If i can Bring this Formula into VBA and add the color changing part, It must only work on the cells listed above..
I have this sheet full of random data and I want to recalculate extra fast so I keep my finger on F9 which causes the random data to randomize really fast of course. Now, in B1:AT1 I have numbers that change with every recalculation but here is the problem. I want the recalculation to stop when excel identifies a zero in that range which doesn't happen often.
View 1 Replies View RelatedIn Cell A1 I have Tectonic 9/4. I would like in Cell B1 Tectonic and in Cell C1 9/4
Similarly in Cell A2 I have Relight My Fire 11/4. I would like in Cell B2 Relight My Fire and in Cell C2 11/4
I can do simple things like RIGHT AND LEFT etc, but that only works on a set number of characters.
I have created a vlookup and it shows as a formula not as data. I can do 'text to columns' to correct it, but I need to drag this vlookup to lots of different cells, then change it slightly in each one.
Every time I make a change, it reverts to the formula and I have to do 'text to columns' again.
I am using a User Form to input data to a spreadsheet and have an issue. I've set the TextBox WordWrap & MultiLine Property to True and the textbox values don't show the reverse P character, but how do I get the "square" from populating into the cells on my worksheet? I was using this old thread as a point of reference, but didn't understand how to use or where to put it in my form. I'm referring to the code that Dave Hawley supplied. Strange Characters Pasting to Textbox
View 5 Replies View RelatedWhen I first started using excell I was relatively good at it, however a few changes were made. The main one being for excell 2002 was the collumns were not by letter. A minor problem that I managed to get past. However the next was a function issue. Before I remembered a way to total rows with a simple function like =b3*c3, which worked for the first one. Then, from that point, I could copy that formula and paste it to all of the cells in the collum in which the forumula changed for individual cells to =b4*c4, =b5*c5, etc.
My questions are simple. Is there a way that I can do this on Excell 2002 without having to do it on a cell by cell basis, and is there a way to change collumns back to letters.
I've attached the spreadsheet in which I want to figure this out on. The goal, is to total price and quantities sold into the totals section without doing it on a cell by cell basis.
Excel 2010. I need to place picture into one cell or one big merged cell, as a background fill. picture must resize to size of cell. must be fixed in, not in front. i still need write into that cell, so it needs to be really background.
View 2 Replies View RelatedI am working on a workbook with 200+ sheets and an index linking to each of the sheets. On the sheets there is a cell containing the results of calculations and I need the cell referencing the sheet in the index to change background colors depending if the calculations value is greater or less than 0. I currently have working code that also changes the tab color of each sheet depending on the value, but i am trying to add the index cell background change functionality into that. also, the index listings is subject to change. my working code is
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("d34").Value < 0 Then
Me.Tab.ColorIndex = 3
ElseIf Range("D34").Value > 0 Then
Me.Tab.ColorIndex = 4
Else
[Code]...
and the code i am trying to get working is
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngX As Range
Set myRange = Worksheets("Index").Range("A1:A500").Find(Range("C2").Text, lookat:=xlPart)
' the C2 is the reference the title that would be on the index
If Range("d40").Value < 0 Then
[Code]....
I keep getting the runtime error '1004: application-defined error or object-defined error the thing is, i ran the cell background changing code in a normal macro before integrating it in my other worksheet code first and it worked fine. I have a little code experience, though not much in vba and I am unsure what the issue is. if there is another better way to accomplish what I'm tring to do, that would also be fine.
I am working on developing QR Codes using some MATLAB code and it would be really convenient if I could create an excel program which changed the background color of a cell containing a 1 to black and a cell containing a 0 to white.
View 2 Replies View RelatedI'm trying to create a tag with a color border. What I desire is to fill the BLANK cells around the tag, A1:D1 + D1:D19 + A1:D19 + A1:19 in a certain color based on the text value of the cell B11. There are 5 different values, such that if the B11 read Red Sox - the boarder is going to be red, if it reads Houston Astros it will be dark blue, etc..
I have a similar problem with changing the color of the cell based on the month. So regardless of the year, 2014, 2015, 2016, etc... If I use MONTH() function I can just get numbers from 1-12. I want Cell C16-C18 to be certain color depending the date entered in cell C17 such that for each quarter, months 1-3, 4-6, 7-9, 10-12 they are different color.
I have had no luck with conditional formatting (and I also believe that it is good up to 3 cases only). I am decent in logic/programming language but have little knowledge with macro notation and especially how to run them in excel 2013. I do know how to start it alt+F11 and that I need to make sure that code is written under the specific sheet where my tag is located.
