to loop through each row in sheets("Layer Layout") and check if there are any red fonts in its cell..If there is, i need to copy the header ("A1") and the rows containing the red fonts to sheets("Report")..
View 9 RepliesI am trying to use a function kindly listed by Dave, that allows for a SUM to be done on cells which have a certain color index. I have modified it very slightly, but unable to set the call to it without getting errors, perhaps someone can shed some light on what i am doing wrong. Dave's original code is
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell,vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function.......
I think I could get this to work but the INDEX command is not returning an index of the row like it is documented. It is returning the value in the cell. In order to get this to work, I need to return the index of the row. I need to know what row the match found the item on because the value I need to copy is going to be in column J of that same row.
I have two worksheets within 1 excel document.1 worksheet is a hand filled and excel programmed worksheet (Name is website-upload)1 worksheet is a hand filled and excel programmed worksheet (Name is website-upload)
1 worksheet is a hand filled and excel programmed worksheet (Name is website-upload)
The other worksheet is a table from a database connection worksheet. (Name is ODBC_Products)
Each worksheet has a Sku column in it.
website-upload: Column AODBC_Products: Column B
Each worksheet has a QTY column in it.
website-upload: Column KODBC_Products: Column J
Goal: I would like to search the Sku column ODBC_Products and find a match for a sku that is listed in the website-upload sku column and then when I find that match, display the qty from the ODBC_Products worksheet to the website-upload worksheet.
Notes: I add columns and delete columns all of the time on this worksheet so I would rather do this in excel vs. a macro. I figure this can be done with something like:
Returns N/A
Code:
=INDEX(ODBC_Products!B2:B1000,MATCH('website-upload'!A2,ODBC_Products!B2:B1000,0),1)
OR
Returns an Error
Code:
=INDEX(Table_database_name_products[productcode],MATCH(A2,Table_database_name_products[productcode],0),1)
Notes:
Here is an example of what I have: [URL]
The website-upload image says Website Qty column (J). That is supposed to say column (K)
I have a SS where I'm trying to copy some data based on the cell color. See Below
Untitled.jpg
For Row 1 I would like to copy all cells where the content/font color is black and paste into K6. As part of the paste I'm also trying to remove the blanks so the end result would be
Untitled1.jpg
I would then like to be able to do the same for the following rows. Not sure if there is an easy way to do this but thought its worth asking.
i have att a workbook with 2 command buttons on sheet "quote"
one turns the selected cell red.
the other one i would to have select the red cells then copy to sheet1 then change the color back to white
i have some problem in display the highlight cell to another worksheet. Below is in module, i would like to display the highlighter cell to another worksheet.
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/2/2006 by user
'
'
Range("I351").Select
ActiveWindow.SmallScroll Down:=-12
Range("I349").Select
ActiveWindow.ScrollRow = 1
Range("E1:E400").Select
I have a detailed note inside biweekly.xls, it explains everything in context.
Two excel sheets, one is updated manually (Vikki's Yearly Averages.xls), while the other is for reporting (biweekly.xls)
The coding will all be in biweekly.xls.
First, it needs to take a date to the right of a name from biweekly.xls and use the month and year from that date for the search.
Then it needs to look in VYK.xls under the name and copy every entry starting in the year specified and month specified and ending at the current date.
i would like to add some sorting code by colors and i need to add the color index into a column in the same file + below formula = colorindexofcell(a1,false,true) i need to exclude 2 columns for deleting the report.To reason for that everyday we run the report and next time there are new entries and report needs to be run again
when i double click anywhere on report sheet it deletes the colorindex and formula column as well is there anyway to modify below code by excluding two rows such as "ag" and "ah" column,for deleting process
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Call Delete_Report
Range(Range("a1"), Cells(Cells.Rows.Count, 1).End(xlUp)) _
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
unquote
I have a spreadsheet which has some cells with blue font. If anyone tries to manually change the contents of these cells a worksheet change macro reads the colour index of the font and, if it is 5 (blue), uses "application.undo" to repair the damage.
When I use the sheet on another computer, the same font colur is being read as a four digit number, 4015 I think from memory, and the code doesn't work.
When I launch excel 2007, i have cells with a grey border. I'm not sure what the colorindex is of this border.
Through a macro, when I format cells, I use
Cells(x,y).BorderAround ColorIndex:=xlAutomatic
xlAutomatic points to BLACK by default. Could someone tell me how I could reference the actual default colorindex (the grey one which seems to border all other cells, while not being xlAutomatic)?
I am editing code that changes a cell's forecolor and I can't seem to find the number for red. What number is red
I have a worksheet with several columns and 1,000's of rows. I have code that makes all "good cells" grey (color index 15) and all "bad cells" red (color index 3).
I would like to do 2 things...
1. If ANY cell is RED, cut the WHOLE ROW and "insert cut cells" below the header row (even if ALL other cells are grey), then repeat the process up the whole worksheet until ANY row with a red cell is at the top.
2. Create a new worksheet named "Trouble Cells", copy the header row along with any rows with red cells.
I would like to keep the formatting the same (for example, the title row is always yellow and is "28" high and all other rows are a height of "12").
I would also like to keep the column width of each column in the new worksheet as well.
Excel 2002
I have a bit of VBA code that loops through a range and looks at the color index. If it is color index three then it will put a "1" in the cell six columns over. This code works, but I see over and over again that loops are bad and inefficient. Since I am working on my code being more efficient I wanted suggestions for altrenate code that would do basically the same thing.
Dim Bcell As Range
For Each Bcell In Range("D2:D304")
If Bcell.Interior.ColorIndex = 3 Then
Bcell.Offset(0, 6) = "1"
Else
Bcell.Offset(0, 6).ClearContents
End If
Next Bcell
Is it possible to have a formula check the color index of a cell?
Example if A1 color index = 6 then X
I am trying to write some code and I am lost.
Column Y-has Y, N entered in cells
Column R-has 1,2, etc... entered in cells (some cells have color index 26)
Column O-has 1, 2, etc... entered in cells (some cells have color index 3)
Column G-has names entered entered in cells (some cells have color index 38)
I need the macro to check if Column Y has a "N" and then if it does check Column R, O, G and if there is no color index in any of the Columns the macro will delete the row.
If Column Y has a "N" but Column R, O, G has a color index it will not delete the row.
There are 3000 rows on the spreadsheet and the macro would need to go through all the rows.
I am trying to come up with a macro that will search a range for a specific string of text plus Interior.ColorIndex = 4. The range can have the text in it multiple times but i only want the macro to fire the next line of it if the cell address also has that color. Is there a way that can be done? My code below stops after it finds its first occurrence of the string and moves on to the next lookup. How do i make it look for not only the value but the index color?
View 5 Replies View RelatedI have got a userform with lots of controls,
One of the action's on a large group of the controls is the same but except for one number
here is an example
If TextBox107.ForeColor = 255 Then ActiveCell. Offset(0, 53).Font.ColorIndex = 3
If TextBox108.ForeColor = 255 Then ActiveCell.Offset(0, 54).Font.ColorIndex = 3
If TextBox109.ForeColor = 255 Then ActiveCell.Offset(0, 55).Font.ColorIndex = 3
This makes a cell that correlates to the textbox red if the text in the textbox is red.
Now, I loads of these textboxes that all need to run the same code with just the Offset value one digit higher than the last and I was hoping I could create a loop to avoid a huge block of code but I can't work out how to make a constant that will +1 with each loop.
Also, can I assume that a loop will start with the control with the lowest number i.e. Textbox1 and then work its way through the rest of them in order?
I wrote a macro to color the cell values in the rows based on their average value. For eg if the cell value is less than 0.2 Avg, they should be red color,if value is between 0.2 and 0.5 it should be yellow. This part is working fine
Now based on the color of the rows cells , need to write a macro for the header one. Logic is Coloum header should be in red colour, if in one or more number of rows cells are red. same with yellow ones. Could you please help me out in solving this with logic.
I have attached the relevant spreadsheet for which I need to alter the color of the columns based on Site number ( Sheet 1). % Mortality will be represented in the Y-Axis, and the Site numbers would be on the X-Axis. All columns (% Mortality) except one will be of the same color, and the one of a different color will indicate a specific site. As an example, site 86 is colored differently. The way I require the chart to look is shown on Sheet 1.
After reading through some great posts on Ozgrid, I managed to do this using conditional formatting (Sheet 2), but that sort of falls short because I am required to add a data table to the chart, and the parameter that is indicated by the column bars happens to appear twice in the data table.
I was wondering if this can be automated maybe using VBA, but with the possibility of simply matching the color of columns with the font color of respective entry in the data series.
I have an Excel 2010 spreadsheet consisting of many worksheets (20 or so). Each of these worksheets contain detail level data regarding different projects. One of the columns in these worksheets is the 'Status' column (column F). There is conditional formatting on this column where if the text is 'G' then change background to a green color, 'Y'=yellow, 'R'=Red and 'U'=Grey.
The first worksheet is a summary sheet that I would like to pull information from each of the detail worksheet's columns B, D, E, G and H if the status column (Column F) is 'R' or 'Y'.
The number of rows in the detail worksheet can change each week (as few as 0 and as many as 100)
This is probably elementary, but I'm struggling and would appreciate any help as I have very little excel VBA experience to draw from.
I have assembled code which changes the cell color based on a value change in Column A. Column A will contain many different groups of repeating values. This code works well and and I have been able to figure out how to limit the number of colors to only 2. The end result is each set of similar values in column A is visually grouped by one of two alternating colors.
The number rows in the data set is variable as the data set is extracted from SAP. The number of columns is fixed.
What I want to do now is set the cell color in columns B through F the same color that was assigned to the row in column A. So if cell A3 is set to colorindex = 6, then I want to set the range of cells B3 to E3 to the same color.
Here is the code I am using to set the color of the cells in Column A:
I have two columns. The first one (A) contains cells that have different Fill colors. The second column (B) contains text adjacent to the colored cells. I am trying to change the color of the text in the second column (B) to the corresponding color in the adjacent cell in the first column (A). I don't think conditional formating works well in this situation. I believe the solution would be some sort of macro.
View 3 Replies View RelatedI have various row cells in column (F) filled with the color Green. And corresponding text in Column G. How can I change the text of that particular row to white.
i.e.: if any cell in column F is Green, change the text color of that row in Column G to white?
I am looking for a very simple script that will achieve the following:
On the clicking of a button, Select and shade in a cell yellow, delete the yellow shading of the previous cell. The shading & selection should move up a column of cells, 1 at a time, in the following order:
From B10 to B9, then B9 to B8, B8 to B7 etc until the selection and shading is at B2. Once it is at B2 subsequent clicks will simply keep it at B2 (the top). Thus after 8 clicks the shading & selection should travel from B10 to B2, with only 1 cell being shaded yellow and selected at any one time.
I have a spreadsheet that i download from the net daily, which is seperated into columns of information.
I want to be able to look down a column and mark a cell in a seperate column if the cell font text is red.
For example looking down column A ... if the font text of a1 is red then mark the cell background colour of T1 red - if a2 text colour is red then mark the cell T2 red .... etc etc.
If the font colour in a1 or a2 ... etc etc is any other colour then do nothing.
I have 5 columns I wish to look down and mark in 5 seperate columns - I have tried to do this by conditional formating but don't know the fomula for checking font colour.
I would like to have cells that conditionally format the font color in the cell based on the background color of the cell. Essentially I have a matrix with some cells highlighted in red, yellow, or orange. There are values in each cell of the matrix. If a value is 0 I want the font to be grey. If the value is NOT 0 AND the cell backgorund color is NOT white, I would like it black and bold. Is there any way to do this in excel or using VB?
View 6 Replies View RelatedSummary of performance of various products against target is as follows,
Product vs Target
Color Code
Result
CH4OH
Green
1.0
[Code] ........
I need the final result automated as follows,
If 2 green of the 4 products, then final result Gree
If 2 Amber of the 4 products, then final result amber
If 2 Red of the 4 products, final result Red
Is there a way to automate this?
I have an "Index" Sheet with all the information and codes for my "Sales" Sheet. I want to copy the code I made with the "Index" Sheet to my "Sales" Sheet if the Criteria has matched.
excel.jpg
I have used this handy formula
=INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A2))
to create an index page that looks like this right now:
Index
35035-9
37032-14
37065-6
37079-4
Now what I would like to do is get cell B1 on each of these sheets to post in the next column without hardcoding the name of the sheet into the formula. So in the cell next to 35035-9, I would like the formula to look at worksheet name, and then go to that worksheet, and return cell B1. Same for all the other worksheet names.
There will be multiple additions of worksheet to this list over the course of the next year and I want to be able to index them easily.
I have a list of words in the column A, and then columns E:SA where are numbers. I need to set up a piece of code/VBA that will index(write) all the words from the column A that have a number 1 on the same row in the column E for example (I need to do this for each column - E:SA).
I know it's hard to understand, I'll give you an example:
Column E has in E16 number 1. So the program will index the text(value) of the cell A16
and so on ... for every column E:SA
I need to index the values in the 1748th cell(and higher) of each column (E:SA)