I used the method described here:
[url]
The problem is that it seems only to work on the computer on which I made the sheet. This should mean that it is somehow dependent on some local settings. Does anybody know what settings, or whether it is something else that may be the cause?
I am trying to band columns together in two's and this formula works except for I need the banding to start with column B.
=MOD(INT((COLUMN()-1)/2)+1,2)
Example:
Column A - no banding
Column B and C - banded
Column D and E - no banding
Column F and B - banded
etc.,
Is there another solution to this without maybe adding a helper row below the data with 0 0 1 1 0 0 1 1 0 0 and do the conditional formatting based off of the helper row?
Is there a formula I can use to locate the last active cell in a column. I did use the count function, however this isn't always reliable if there are blank cells within the column
View 5 Replies View RelatedI know that if I select say A1:C100 on a spreadsheet, I can color alternating rows using conditional formatting and the formula =MOD( COUNTA($A$1:$A1),2). I am using COUNTA -- rather than simply ROW() -- so that I am only considering visible rows. I am trying to write a VBA subroutine that takes in an arbitrary range and colors in every other row of that range. Something like:
Sub FormatRange(ByRef theRange As Range, ByVal theColor As Integer)
theRange.FormatConditions.Delete
theRange.FormatConditions.Add Type:=xlExpression, Formula1:= "=MOD(COUNTA($A$1:$A1),2)"
theRange.FormatConditions(1).Interior.ColorIndex = theColor
End Sub
However I want to make the argument to COUNTA refer to the first column in theRange, not necessarily column A in the spreadsheet.
Am trying to return the color index of any cell my formula is in, with no VBA
Am using the FILLCOLOR function below....
Public Function FILLCOLOR(cell) As Variant
Application.Volatile True
FILLCOLOR = cell.Range("A1").Interior.ColorIndex
End Function
.... and wanting to combine it with Cell("address")
However, all I get is an error which I believe comes from Cell("address") returning "$A$1" (in quotes) and not $A$1 (no quotes).
Is there VBA code that will:
a) change the color of the active cell?
b) return the formatting back when that cell is no longer active?
Here is a screen shot of what I have:
I want to be able to enter the name from the above 4 cells (B3,B4,B5,B6) IN THAT COLUMN, and have the active cell change color accordingly.
If I am in cell C7 and start to type "PUBLIX" It should turn red in color. It does not because the code looks to a specific cell (example: B3). What can I change in my code to give the results I desire?
I have conditional formatting code below I copied from THIS site:
Private Sub Worksheet_Change(ByVal Target As Range)
' Multiple Conditional Format
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Adjust Format range to suit
Set rng = Range("B7:H74")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Adjust conditions to suit
Select Case Target.Value
Case Range("B6").Value
Target.Font.ColorIndex = 50 ' Green
Case Range("B5").Value
Target.Font.ColorIndex = 3 ' Blue
' Case "Super"
' Target.Font.ColorIndex = 6 ' Yellow
Case Range("B4").Value
Target.Font.ColorIndex = 13 ' Purple-ish
Case Range("B3").Value
Target.Font.ColorIndex = 5 ' Red
' Case "Corporate"
' Target.Font.ColorIndex = 37 ' Light Blue
End Select
End Sub
Found code on this site to change the color of the active cell. One problem and two questions. Code works, however, when you save and close the last active cell is stuck with the new color. Question1 is it possible to limit this code to cells in column B only and fix the problem above? Is there an easier better way to get the same result. See Attached
View 2 Replies View Relatedchange the font color of an active cell, having it going back to its previous one when you move to a different cell?
View 9 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.
i am having problems with the conditional formating function.
to make document i am working on for office a lot more clear i was hopeing to do some row banding. i think you can get some basic row banding in auto format but i was hoping to do it myself.
when i go to conditional formating - i change the tab to Formula is: ...
I found Change Color Of Row When Cell Is Selected ("search" is my friend).
StephenR's example is just the sort of thing I want, except I'd like it to ignore Cells that already have an Interior colour, or revert the Cells to their original Interior colour when you move on. The other issue I see would be how could you change/edit a Cells fill properties, without this routine changing it back to white when you move on?
I have a large workbook where the tab colors differ. I don't want the first few sheets or the last dozen or so to sort but I want to be able to activate/group numerous sheets and then run a macro that will take those sheets and sort them by color.
View 4 Replies View RelatedWe have a customer rebate in place with various levels of refund based on the quantity purchased during the year. I have used a sumproduct formula to calculate this before.
The customer used to have the following set up -
0-999 - £1.00 per unit rebate.
1000-1999 - £2.00 per unit rebate.
2000-2999 - £3.00 per unit rebate.
So if they bought 2501 units they would get a rebate of (1000*1)+(1000*2)+(501*3). However the customer has trigger points so rather than the above it is now -
0-999 - £1.00 per unit rebate.
1000-1999 - £2.00 per unit rebate.
2000-2999 - £4.00 per unit rebate if 2500 bought.
So now it would look like this - (1000*1)+(1000*2)+(501*4). However if they only bought 2499 units it would be (1000*1)+(1000*2)+(499*2).
whenever the active cell is within a given range, highlight the cell on the same row in column S (by changing its interior colour). This should occur each time the active cell is changed, whether by cursor keys or mouse. The effect would be similar to the row and column highlights at left and top of the worksheet.
This action should be restricted to one sheet in the workbook.
It's for Excel 2003.
I have got stuck on one piece of my code and having trouble fixing... Overall I am trying to find variable station name in cell L2 of Sheet 2 in Sheet 1 and then select and copy the data from the data in "cell L2 of Sheet 2" to the last entry of that row. I have attached an example test spreadsheet of the data and a macro is within Sheet 1 called test1. Please note that cell L2 in Sheet 2 will always be different station name and the station list in Sheet 1 will change with differing station name.
The code I am using is:
[Code] .....
The code that is not working and bringing up an error is:
[Code] .....
Attached File : Copy of Testexample.xlsm
For obvious reasons, the conditional formatting to shade alternate rows doesn't work when filtered. So I think I need another way of doing it. Luckily, my table is fairly static, rows aren't added or removed. The first column is excluded from the banding. As is the first and last row with data (1 & 67) respectively. I found a relevant thread here, but the code is beyond my understanding.
View 3 Replies View RelatedI am trying to append about 15 files of CSVs. I have code that works on importing the data, placing it at the end of the previous data, but then it clears the previous data. Here is the code
Sub import_BCDV()
Dim lastrow As String
Selection.End(xlDown).Select
Selection.End(xlUp).Select
' Range("A515").Select
lr = FindLastrow1()
lastrow = "A" & lr
MsgBox lastrow
Range(lastrow).Select
' "TEXT;J:QA ReportsQA ReportsWorkbenchBCVD 1-11-09 1-17-09.csv", _..........................
I have a Workbook (only 1 sheet) that is filled out each month and consists of sales and revenue data. I need to copy the total sales and total revenue and paste them to another sheet. The only problem is that it is never consistent as to which cell the totals will be located in. They will always be in the same column, but almost never in the same row.
For example, one month there will be 8 individual sales and the total will be in Cell F13, but the next month will be F20. THe same needs to be done with Total Revenue (which is always one cell right of the Sales Total) I need to copy and paste the cell with Total in it (in this case F13) and do the same for each month after. This is not difficult to do in Excel, but i need some code to include this process in a macro.
I want to look at a whole colum and to tell me if the 6 digit number is located within it.
is "787897" located in column D? if so tell me by either Y / N or 1 / 0.
I have a spreadsheet formed from a text file.
I want to search for each instance of "DOG" in column A, and copy the row below it and then paste that entire row to a new sheet called DOG. I can then autofilter and use a pivot table on the DOG sheet.
I cant just use an autofilter, because the row below can and sometimes does appear elsewhere in the spreadsheet, but refers to something else. It is all because this is an imported text file.
I have attached the spreadsheet to show you what I mean.....
here is the code I have so far, modified from another thread I posted.
Sub dogs()
Dim rd As Worksheet, dg As Worksheet, i As Long
Dim fRng As Range, Crit
Application. ScreenUpdating = False
Set rd = Sheets("Pets")
Set dg = Sheets("Dog")
The attached is a very cut down version of my spreadsheet...
Here is the sheet where I entry new data. From left to right: Productnumber/article/supplier/price per item/starting nr of articles/order threshold/ date 1st order/date of delivery. When I press the button "verwerk" the data will be transported to the sheet "Voorraad verloop" shown underneath. As you can see the first problem consists of the placement of the first table. I can't get it to the first column. The second problem is that when I enter a new product like in the first image, it overwrites the current data in the sheet "voorraadverloop". See the image below. I used the following macro:
Private Sub CommandButtonVerwerknieuw_Click()
Dim iLC As Integer
Dim FindString As String
Dim rng As Range
Dim Lr As Long
If Application.WorksheetFunction. CountA(Range("A4:F4")) < 6 Then
MsgBox ("Niet volledig ingevuld"): Exit Sub
End If
FindString = Range("A4")
If Trim(FindString) <> "" Then
With Sheets("Voorraadscherm").Range("A29:A65536")
Set rng = . Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _ .................
Summary 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 need hyperlink to pdf (as this is given in one block how to give hyper link to pdf file).
I want to give give pdf hyperlink in in excel to locate the pdf file with specific page or topic number in pdf file (like pdf file name is 'ABC' and page number '10')
Can i give this type of specific link in excel?
I am trying to use VBA in Excel 2007 to change bar chart series colors. I have found a few posts that link it to a cell background, but I'm struggling to find one that does the font color.
It would be great if I could change the bar chart series to match the color of the text in the A column, so that if I highlighted the value in A1 and changed the text color to orange for whatever reason, the chart updates the value of 1.2 to an orange bar (see below).
T. A1 B1
Sample 1 1.2
Sample 2 2.1
Sample 3 1.7
Sample 4 5.6
I have a range of cells that change colors with conditional formats based on the cell value from high to low. I would like to link the cell color to an object such as a circle or rectangle. When the cell value changes along with the conditional format, the color of the object will also change.
View 3 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'm trying to add a textbox at the current position (selected cell) with a set size, fill color, and border color. I found this: http://msdn.microsoft.com/en-us/libr...8(VS.80).aspx:
View 2 Replies View RelatedThis 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 would like to be able to change the color of a cell in V4:AB31 and have the formula in AM10:AM13 automatically calculate the new result. As it is now the user has to press Ctrl ALT f9 for the formula to recalculate.
View 7 Replies View Related