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 RelatedWhat is the syntax for centering in the screen whatever cell a value has been changed in or needs the users attention?
View 9 Replies View RelatedI 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?
change 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.
The colour cannot display in the cell after i changed it.
however it will appear in e print preview.
EXPENSE MASTER 2013 sample color.xlsx
I have numbers that will display in column G. I have payment types entered in column E. So if 'C FUEL','FA','C M/C', is entered in column E I want the number in column G to be red.
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 RelatedI have a Excel database and a userform to look-up reocrds. I want to be able to connect a Data Sheet which is a PDF file to every product in the daatbase.
I have created an additional column and created hypelinks in front of each product. these hyperlinks are connected to the datasheet of that product which is saved on the drive.
I have created a field on the userform to display the respective hyperlink when a record is searched by user.
My problem is that the user form displays the hyperlink name, but its dead.
I want users to be able to click on the hyperlink to view the attached Datasheet. I have tried using Textbox and labels to display this hyperlink,
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
I have a column of numbers that I am trying to get to display based on the value. (The field is formatted as percentage and must stay that way).
I want any number that is < 90% to appear red, but any number => 90% to appear black.
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?
Example:....
i m wanting Cell B1 to display true if any of the values in column C match otherwise I want to display false.
A 1
B 0
C 0
D 0
But I can't determine how to get this done.
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 RelatedI 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 font color white in blank cells in column E and I (from row 5 to row 245) so the visitors will not see the text. If any of these cells become yellow (color code is 6), the font color will become black so visitors can see the text alot far better than white. I've tried this code myself after this post but nothing happen
View 4 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?
if it actually possible to colour data points depending on the colour of the cell of the data it refers to? if not is it possible to colour it any other way
i have uneven amounts of data for weach month but still want the months to be displayed, this is the best way i can think of of doing this
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've reached a problem with colors in Excel: need to convert cell color (get from .Interior.ColorIndex property) into RGB value (to set ForeColor of the button). How can I make it?
View 8 Replies View Related