I 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 trying to color a cell with a macro. I am using
ActiveCell.Offset(a,b).Value= a number then a want to change the color of the cell to yellow. When I tried to code this, my Active cell's color is changed, but not the offset cell.
How do I change the color in an "offset cell".
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 RelatedI'm using Excel 2003- and I am trying to change the fillColor of a cell based on the value of that cell.
11111122222222233333333344455555555555556666777888
If I use Conditional Formatting I can only use 3 colors, I'd like to use 8 or 9. Is there another way to do this without using the Conditional Formatting?
I'd like the result to look something like this-
11111122222222233333333344455555555555556666777888
I've looked at the similar threads in this Forum, but I couldn't find exactly what I needed.
I have a worksheet that when a row changes based on the value of column B, I want to remove all of the formulas found in the row but keep the existing values, and then change the color of the row.
In the sample file attached, when the value is "Closed", that row will keep the existing values and then it gets grayed out. Rows that are still marked "Open" need to retain the formulas in case other information changes.
I have tried copy/paste special using autofiltering but that doesn't work because of the hidden lines. This file changes on a daily basis and I need a quick way to update the file.
SampleFile.xlsx
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?
Using Excel 2010. Is it possible to change the default line thickness and fill color when selecting the ActiveSheet Target Row below?
I would like a thin border and a light grey fill - without interfering with any fomatting or conditional formatting that has been applied to the worksheet.
Function BG(InRange As Range)
Range("InRange").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Function
That so far but not quite sure why that isnt working. I want to change the fill color with a UDF that all they do is select a Range and it changes those fill colors to whatever the Colorindex may be. I didnt find anything while searching the forums with this already.
ThisWorkbook.Sheets(2).Cells(nrow + 1 + j, 2 + high_count).Formula = "=VAR(" & ActiveCell.Offset(0, n_high_count -1) & "," & ActiveCell.Offset(0, -1) & ")"
*n_high_count is the negative of high_count
I want "ThisWorkbook.Sheets(2).Cells(nrow + 1 + j, 2 + high_count)" to have the formula "=VAR(XX:XX)" where the range is the current row second column to the current row current column -1.
I need to make the active cell go to the beginning of the row (Column A). I tried setting up a variable to be the row.value, but that didn't work (the way I wrote it, which doesn't surprise me).
View 6 Replies View RelatedI'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 RelatedI have a simple spreadsheet. A column for a persons name and 31 columns, one for each day of the month. I want to apply conditional formatting, either fill color or border color, to the date column of the current date when the spreadsheet is opened.
View 2 Replies View RelatedI have 2 columns of data
column A = weeks (A2:A50) i.e. P1W1, P1W2, P1W3....P12W4 etc
column B = headcount (B2:B50) i.e. 5, 7, 5...10 etc
Essentially my criteria is "looking for last week and give me headcount" i.e. my lookup criteria is P7W5 ....however if P7W5 has no data, i want the lookup to go up or offset to the row above (it may be one to 4 rows above)....
I have a multi-worksheet workbook that has many forms control checkboxes throughout it. I'm looking for some VBA that will change the background (fill) color of ALL the checkbox when it is checked (True). I've seen code for a single checkbox, but not multiple/all boxes. I know just enough VBA to be dangerous, but I'm up to learning anything new.
View 3 Replies View RelatedI have a formula in cel B1 : =SUM(A2:A100) / A1
I would like to use this formula many times in the sheet, so I would need a reference to the cell the formula is in, and have the SUM range until the next empty cell one column to the left.
So I would need something like (literally):
=SUM(Offset activecell (1,-1) : Offset activecell (1, (look for next empty cell -1)) / Offset activecell (0,-1)
I am desperately find a way to fill down formula from active cell, example given below,
AA
BB
CC
Total
QTY1
QTY2
City 1
5
2
3
10
12
15
[Code] .......
I need to fill down col Qty 1 & Qty 2. Number of Rows and Columns vary in my Work Sheet.
My code below, Ctrl+Enter not works.
HTML Code:
Cells.Find(What:="QTY1").Activate
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-3]"
'Fill Down Active Column
Cells.Find(What:="QTY2").ActivateActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=+RC[-5]*RC[-3]"
'Fill Down Active Column
End Sub
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 RelatedAm 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).
I am attempting to write an Excel Macro that would allow me to Click on a particular row and have the fields in my form fill with the data in the columns of the row.
I am fairly new to Macros and VBA, but know that this has got to be a possibility.
I 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?
Get value from another cell using Offset and active cell. I can do this in VBA but I cant seem to do it on a worksheet.
View 4 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'm trying to make a simple chart with VBA based on a row with values that will color the offset cell interior red and also give it a value of 1. (look a the example sheet.)
View 4 Replies View RelatedI have a question, can I fill a cell with a color in VBA? If is that possible, how can I do it?
View 2 Replies View RelatedI am using this formula:
=IF(INDIRECT($E$1&"!$B4")=0,"",IFERROR(INDIRECT($E$1&"!$B4"),""))
Now, if that cell happens to be blank, I want it to be grey. But if data is drawn into the cell, I don't want it to be grey.
