Auto Change Row/font Colour And Move Row
Attached example sheet. Basic table of data, with column F being a validation list, is it possible that when choosing COMPLETE from the list, the row and font change colour, and then move to the top of the list?
I'm not the only simpleton using the sheet so I need it to be as simple as possible. I know it doesnt seem like a big thing but the sheet we are working from is huge and I dont want people cutting and pasting away, I just dont trust them with my spreadsheets.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Change Font Color Of Last Row
I am wanting to be able to find the last row of data (range changes on a monthly basis) and change the colour of the font to white for the entire row. I don't want to delete the row as contains totals but want the font not visable.
View Replies!
View Related
Change Row Colour When A Value Is Given In A Cell
I dont have that great knowledge in Excel but i wanted to try and run a macro in an IF formula but realised that was not possible. I have now got a formula that gives a value in a cell when i want the whole row to turn to red, but im not sure how i can get a macro that will change the whole row to red when that cell shows a value. Can anyone help on this matter?
View Replies!
View Related
Change Font Colour With Different Values
I'm creating a simple spreadsheet totalling units of alcohol up. Is it possible to change the colour of the fornt when different values are reached? For example if a total of under 8 units is reached this is displayed in green, if over 8 the figure is shown in red.
View Replies!
View Related
Auto Change Row Color After Cell Data Input
Not sure if this is possible or if I would need to run a VB macro. image multiple columns with text headers. 'A'=Project Number 'B'= Date 'C'=Description etc etc etc 'S'=Cancelled (i actually use up to 'Z' on the columns for various other data) I have added a Conditional Format on the cells in column 'S' that if the cell data = Y then colour the cell RED. This at first glance will show that the project has been cancelled. What I would like it to do is to colour the complete row (shaded grey for example) this would then show up more easily with the amount of data that is on my sheet.
View Replies!
View Related
Insert Row On Sheet & Move Active Cell Row To It
I would like to create a macro that could archive entries from one sheet and insert them in another. I created one but the problem is that the entry has to be the same row each time. Example: Sheet 1 – is current jobs and sheet 2 is old jobs. My macro moves an entry from Row A-5 of Sheet 1 and moves it to the top of Sheet 2. I would like to be able to scroll through each entry select it and have it moved to the top of the Old Jobs sheet.
View Replies!
View Related
Comparing Two Cells Based On Text, Font, Colour, Font Size Etc
I'm looking to set up a spreadsheet whereby individuals answer questions and have to format their answer using a particular font, colour, font size and so on. The idea is that I can then compare their answer sheet to a pre completed one using an =IF function and get a total score. The only problem is =if and =exact only lookup cell text/numbers and don't look at how the text is formatted within them.
View Replies!
View Related
Macro To Format The Auto-Sum Row Without Knowing What Row# It Will Be
I currently have a macro setup that puts Auto-Sum data in the row below the bottom-most row of data. What code would I need to format this Auto-Sum row with the following parameters automatically... 1. Merge & center F & G in the Auto-Sum row with the text "Totals". 2. Change the row height of the Auto-Sum row to 44 pixels. 3. Center the vertical alignment of the Auto-Sum row. 4. Put the thickest border around the Auto-Sum row in cells A-K.
View Replies!
View Related
Change/Move Pivot Table Row Field To Column Field
In building my pivot table my data that I want to show in the column area is showing up as rows stacked on top of each other. In the column section I'm trying to show Total Budgeted Amount next to Total Actual Amount but on the layout it's showing the two stacked on top of each other is there some kind of hidden key that I'm missing?
View Replies!
View Related
Auto Insert A Row With Info Populated From The Row Above
to be able to do is have a stock control sheet that tells me how much of 1 item we have in stock which is easy, the hard part comes when this stock is sold as this stock can be sold to a number of different customers e.g. We have 10,000 X Pens Customer 1 buys 1,000 Customer 2 buys 6,000 Customer 3 buys 3,000 i need a way of saying that we have 10,000 pens but if we sold 1,000 then we can click a button (macro maybe) and that will automatically see that we have 10,000 for that line and we have only sold 1,000 so we need a line inserting saying that we have 9,000 remaing and so on.... The sheet needs to record who these goes to though, that the reason i was thinking of adding a row If this doesnt make sense then i am happy to answer your questions? i can upload an example excel sheet if required if someone can explain how i do this?
View Replies!
View Related
Match Amount And Colour Row
I am trying to match by amount with 1 column to another in a different sheet. If Data in column A in worksheet 1 matches data in column b worksheet 2 then color the entire row in column A worksheet 1 red, I am matching by amount so it(amount in column a) can be within 1 $ of amount in column b worksheet 2 also color item in column b(worsksheet2) red if matches with column a in worksheet 1 both can have a threshold of 1$ difference
View Replies!
View Related
Setting Row Height To Font Cause Hidden Rows
I generated my spreadsheet in using vb6. Everything is working except that when I display the display (making it visible), some of the data in the rows hides underneath the row lines. I have to physically go into the spreadsheet to expand the rows to see the hidden data. Also, if I print the spreadsheet, the hidden part of the spreadsheet is cut off unless I mannually expand the rows. How can I programmatically expand the rows to fit each amount of records in the row. Currently, I have a module that is mannually calculating the rows Also, I have attached the spreadsheet so that you can see my output. Below is the code that calculates and formats the row presently: w = 0 For Each R In xlWksht. Range("A5:N5"): w = w + R.ColumnWidth: Next rht = xlWksht.Range("A5").RowHeight With xlWksht.Range(xlWksht.Cells(ii + 1, 2), xlWksht.Cells(ii + 1, 14)) .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .WrapText = True .Orientation = 0 .MergeCells = True .RowHeight = .Font.Size * (Len(xlWksht.Range("A" & ii + 2).text) - Len("Comments:")) / w + rht + (rht - .Font.Size) ' + newlinecnt * .Font.Size End With Below is my entire module: ii = 5 w = 0 For Each R In xlWksht.Range("A5:N5"): w = w + R.ColumnWidth: Next rht = xlWksht.Range("A5").RowHeight 'Do Until M.qBW.EOF = True Do While Not M.qBW.EOF ii = ii + 2 xlWksht.Cells(ii, 1).Value = M.qBW![Req No] xlWksht.Cells(ii, 2).Value = M.qBW![Description] xlWksht.Cells(ii, 3).Value = "" xlWksht.Cells(ii, 4).Value = M.qBW![ClientName] & Chr(10) & M.qBW![Status] xlWksht.Cells(ii, 5).Value = M.qBW![P L] & Chr(10) & M.qBW![TotalProg1Hrs] ...
View Replies!
View Related
Conditional Formatting - Colour A Whole Row Of Data
I am trying to colour a whole row of data (in columns A through to AA) based on the numeric entry in column AB. The numeric entry is either 1, 2 or 3. I reckoned I needed to use the IF function, but as writing formula is not my strong point I am struggling to enter the correct data! The first row of data I wish to use is 3. I have tried the following but to no avail: =IF($AB3,1). =IF($AB3=1). =IF($AB3="1").
View Replies!
View Related
Colour Row To Denote It's Use In Autofilter
I am using autofilter and then copying some cells from the rows that are selected as a result and pasting them to another worksheet within the workbook. My problem is that in the worksheet that contains the autofilter I need to colour (or index?) the row that I am copying to show that it has been used. I tried UsedRange but got in a muddle.
View Replies!
View Related
Using VBA To Select A Row And Colour Code It Based On Conditions
I've had a look through the forum and can't see an answer, so apologies if I'm duplicating something! I have a spreadsheet where column O has five conditions: 1 PO in 2 High Probability 3 Medium Probability 4 Low Probability Blank I need to colour code the whole row based on the value in the cells; if there were four conditions, I could do this with conditional formatting, but with five (with white as the fifth condition), I need to look at VBA. However, I'm having difficulty colouring the whole row; how do I do this? e.g. what I need to do is: If O2 = "1 PO in"; Select A2:T2; Colour green In addition, I need to ensure that if there is no data in column D, but column O is "1 PO in", it is highlighted in a separate colour. I have a macro for that, but don't know whether in needs to be put into the code before or after the macro needed above...
View Replies!
View Related
Move Row Up Or Down
I have a workbook with several sheets each containing different numbers of rows. These rows contain data from clients (codes etc applying to motors). The rows have been randomly populated and I am having to move rows from somewhere in the sheet up or down so the codes are grouped according to which motor they apply to. What I want to do is select a row and be able to run a macro that will move it where I want. The destination can be up or down and any number of rows away. Does anyone have any ideas how to do this? I was thinking that if I selected the row I wanted to move and the one I wanted to move it to then run the macro it could be moved. Its not to replace the destination row, but be inserted alongside it.
View Replies!
View Related
Counting Cells By Font Colour
I need to count cells in a column that display a red font as a result of conditional formatting triggered by the MAX function in adjacent rows. I've tried numerous VBA codes suggested by your site and others but without success. They work if I manually change the font colour but not when they're changed conditionally.
View Replies!
View Related
Worksheet Background And Font Colour
when i pasted the data into an Excel sheet, the background will change to red and the font will turn to blue. Besides, the font in header will change as well. I can't find the way how to do it because this template is set by other person. I can confirm this is not done by macro.
View Replies!
View Related
Worksheetfunction.text() And Font Colour
The following code does everything I need it to except for some reason it is not sending the font colour through first or the font colour is getting overwritten. I have formula in other worksheets that are counting the red and the blue to determine which rider in a team it is. For some reason the time sent to "B Grade" worksheet is ending up in black font. Can anyone explain to me why this is occuring. I am using worksheet function.text because I am working with elapsed times that could be over 24hrs. I was wondering if the default text colour for the worksheet.text() function is black and if it could be changed.
View Replies!
View Related
VBA To Move A Row Up When Value Is Not Zero
I have 8 rows (B345:B352) that have a formula similar to this:=IF(Site1!B53>0,CONCATENATE(Site1!$B$53," Scansys 942 @ ","$",Site1!D53,".00"),""). By the way, can the formula be shortened? I tried formatting the merged cell but nothing changes. When Site1!B53=0, then there's nothing in the cell.That's how I want it. But say, row 3, Site3!$B$53 >0 how do I programatically move (copy/paste?) row 3 to row 1 (since row 1 and row 2 are blank)? In other words, Since rows 1 & 2 is blank, I don't want the two empty rows there, so I would like to move row 3 up to row1.
View Replies!
View Related
How To Move To The Next Row In A Do Loop
In a macro I made, I have a do...while loop: Do While Not objXLworkBook.ActiveSheet.Cells(nSequence, 1).Value = gcstrEMPTYSTRING .....bunch of code Loop In my file, the second row of data always contains blanks (i.e., I've got a HDR record, blank row and then rows of data). How do I make my code bypass that empty row of data and then continue processing all other records? Should I put a "move to next record" in my bunch of code, and if so, how do I do that?
View Replies!
View Related
Move All Used Cells On Each Row
I have rows of data starting in row 4. I have row Headers taking up columns A:G. My data, which is downloaded, can be anywhere between H4 and IV900. I need a macro that will go to G4 Then do the equivalent of arrow right to the first cell containing data Select that cell and all cells to the right of it Copy the selected range Cut and Paste it back to H4 Move to G5 and repeat all the way down the worksheet so that I end up with al of the data starting in column H rather than spread all over the worksheet.
View Replies!
View Related
Find And Add Based On Font Colour
I humbly seek help on the "Find and Add based on Font Colour". For instance, A1="urgent"(with red font) and I want B1 to return as "ATTENTION". Other than red font, it will return as empty string. Can Conditional Formatting do the task?
View Replies!
View Related
Deleting Rows Based On Font Colour
I have 11 names, one name in each cell, in one row. I want to be able to delete a row if there are more than 3 names in that row that have the same font colour, eg red. Would have to be a loop type macro to check all of the rows on the one worksheet.
View Replies!
View Related
Amend The Font Colour Of The Any Captions On The CommandBar
I have create a Command Bar, my problem I'm trying to understand is this 1.Can you amend the font colour of the any Captions on the CommandBar? 2.How can I get the Caption to pick up from a range and use the range name as the Caption? I have named a range in a worksheet that I want to pick up and use as the range. Below is my CommandBar code
View Replies!
View Related
Move Cell Value To Last Used Row Of Another Workbook
I have recorded a Macro but cannot figure out how to edit it to accomplish my goal beyond the first use. I want to to Highlight a Row in Worksheet A, ( Amazon Open.xls ) - Run my Macro to Cut that Row -- then Open Worksheet B (Amazon Sold.xls) and Paste that row into the next unused Row. Then copy Cell L in that row to clipboard. Heres what I have recorded -
View Replies!
View Related
Move To End Of Used Range In Row
Is there a key combination to move Right to the end of the row even though there may be blanks within that row? If I do a Shift>Ctrl>Right arrow it will highlight to the cell just before the first blank cell. I am trying to "Move" to the Right end of the table reguardless of what cell I'm in or if there are blanks in the row.
View Replies!
View Related
Move All Rows 1 Down But Skip Row 26
I've got a pretty nice Worklist setup in Excel that enables you to organize all the work you need to do in a month. However i end up with little holes in the list whenever i finish a task because what my macroes does is whenever a job is marked as done on the list it moves that job to sheet2, which contains completed jobs. I'd like to have a macro that shifts all the rows 1 down whenever a job is completed(ill just call it from the job complete macro) but it needs to jump over(skip) row 26 because that row contains some images that my macros use. Altso i would like to, if possible avoid inserting any rows or cells or hiding them because that will totally mess up my macros :p I know i know, im not dynamic enough. I've included a sample of the worksheet so you can see what i want. By the way, I've altso got some trouble with a public function.
View Replies!
View Related
First Row (for Column Titles) Not Move
I have been given an Excel file. Upon opening it, I see that the top four rows do not move even if I scroll down. What I'd prefer is that only the first row (for column titles) not move. If I can learn the name of this feature, I'm sure I can figure out how to change the file.
View Replies!
View Related
Macro To Move Row To Another Worksheet
I have a workbook with two worksheets in it. The first "TO DO" and the second "Completed". In "TO DO", I have rows of tasks starting in row 4 (row 3 is my header), going to 200. What I'd like to have happen is when I put a "C" in column C, it moves the entire row to the "Completed" worksheet, greys it out and removes it from the "TO DO" worksheet. Then if I remove the "C" from the "Completed" worksheet, it moves it back to the bottom of the list. I already have a macro to resort it based on "priority" in column E.
View Replies!
View Related
Move Row Based Off Criteria
What I am trying to do is if on the (Open Project Issues) tab column F says "Closed" it will move that row to a tab called (Closed Project Issues) tab. I was trying something like this: Sub testmove() Dim c As Long For c = ActiveSheet.UsedRange.Rows.Count To 2 Step -5 If Cells(c, 6) = "Closed" Rows(c).Cut End If Next c.......
View Replies!
View Related
Move All Data Rows Up 1 Row
I created a macro that is supposed to copy the contents of about 500 rows, and move them all up one row. One of the things that is contained in those rows are a bunch of links to Microsoft Word documents. When I try to move the 500 rows up manually (just copying, and pasting one row higher), it works fine. The link remains entact. However, when I use the macro I recorded, the link no longer works. It looks like it is there (the text used to represent the link is there), but when you click on it, nothing is there. Here's my code....
View Replies!
View Related
Move Selected Row To Another Worksheet
I have a workbook with multiple sheets. I need to create a macro and associate it with a command button that will cut the row the user has selected and paste it into the last empty row on another sheet in the same workbook, then, go back to the first sheet and delete the empty row.
View Replies!
View Related
|
TRACKER
