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?
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 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.
In my Excel 2003 worksheet, I need the row color to automatically change to blue (color 5) (bgcolor = #0000FF) - when the user changes the text from VALID to INVALID in the range: B3:B65000.
For example:
Cell B5 contains the text: VALID
When the user changes the text in the field to read: INVALID - then I need the row range: A5:W5 to change to the color blue.
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?
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).
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 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 Relatedan event macro to change the font colour of a cell whose value changes as a result of a calculation.
View 9 Replies View RelatedI have a drop down sub pasted to worksheet:
Private Sub ComboBox1_Change()
ComboBox1.List = Array(100, 200, 300, 400)
If Range("I11").Value < Range("N11").Value Then
If Sheets("Profile").Range("K18").Value < ComboBox1.Value Then
Range("I11").Interior.ColorIndex = 2
Else
Range("I11").Interior.ColorIndex = 3
End If
End If
End Sub
I want it to change the cell color on drop down change. How can I modify things to have the change in drop down selection?
I need to input a long list of steel sections in Excel. The steel sections have names like HEA200, HEA120, HEB 400, HEM300 etc.
I was thinking that this could be done easier by using codes for the section prefixes (HEA,HEB, HEM etc) and that the cell value would change automatically.
HEA=1
HEB=2
HEM=3
HD=4
UNP=5
etc.
For example:
I input "1100" - this would change on the fly to HEA100
I input "2200" - this would change on the fly to HEB200
I input "5200" - this would change on the fly to UNP200
I know I could do this by using a lookup function in another cell, but I want it all to happen in the same cell.
I'm trying to enter this very simple macro that is supposed to switch from one active window to another. However, I'm getting an error, which I can't quite figure out. Here is the
View 2 Replies View RelatedSay i have a Worksheet named "gateway" or sometimes it will be "gateway (2)" (3) and so on. Is there a macro that i can call that in some ways calls the active worksheet and renames it to just "gateway" everytime?
View 2 Replies View RelatedI have a column with entrys of 2 kinds. Some being Hyperlinks and others with normal type data. All cells are locked with password except for cells that will possibly have entrys made in them.
Is there a way to move the active cell to R1C1 after any hyperlink clicked? R1C1 is unlocked.
I am still kind of new to using VBA code and my editing skills are very novice at the moment.
How would I change this Code to run on only the active cell selected and that is all, instead of the whole page?
Sub AddIFERROR()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim xCell As Range
Dim xFormula As String
For Each xCell In Selection
If xCell.HasFormula Then
xFormula = Right(xCell.Formula, Len(xCell.Formula) - 1)
xCell.Formula = "=IFERROR(" & xFormula & ","""")"
[code]....
I have a workbook with about 12 worksheets within it. I am trying to input a button on worksheet 3 that when pushed would activate worksheet 12. Basically the same as if you clicked on the tab at the bottom of the screen called worksheet 12.
I realise that you have to create a button which Ive done. However I cannot seem to figure out the proper macro code to get the button to change the current worksheet when its pushed.
The attached sample workbook has a listbox of areas in a userform. The Textbox on the user form is so that the user can edit the Area names in the listbox. The userform functions fine as long as the column that contains the area name is not hidden. What I want to be able to do is edit the Area Names using the Textbox with the area name column hidden.
View 2 Replies View RelatedI have a button on say, sheet1 with text that I want to change after a certain action takes place; however, I was trying to change this text without switching sheets. (switching sheets isn't a big deal, I'm mainly trying to do it my way for the practice). I'm able to select the button on the other sheet without actually switching sheets, but when it comes to changing it's text I get an error. Here is the code I tried:
Sub macro1 ()
Worksheets("sheet2").Select
Worksheets("sheet1").Shapes("Button 2").Select
Selection.Characters.Text = "Done"
End Sub
Problem with this is it gives cell A1 on sheet2 the "done" text. I also tried this...but it gives the error.
Sub macro1 ()
Worksheets("sheet2").Select
Worksheets("sheet1").Shapes("Button 1").Select
With Worksheets("sheet1").Shapes("Button 1")
.Characters.Text = "Done"
End With
End Sub
As i navigate excel, I was wondering how to change the background or highlight (instead of bold border) the active cell?
View 3 Replies View RelatedUsing 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.
What I would like to do is to have the color of a tab change as the dependant upon the value of one or more cells.
View 9 Replies View RelatedTry to find a way to automatically change the color of a row, based in changing the name in column A.
I tried conditional formatting but the name (or dates) in column A are not the same (always other and other).
I failed to adapt any VBA CODE (found in GOOGLE) because column A varies a lot.
Annex a photo and an excel example.
EXAMPLE FORUM HELP.jpg
EXAMPLE 1.xlsx
What formula do i use if i want the text in cell A1 to change to red if any entry is made in cell B1.
View 2 Replies View RelatedI am having some color problems. From A2:AB2000 I have different fill colors for cells all over the place. The only color I need is the color purple which I believe is the color “39”.
So basically all cells except the cells with the color purple change to white from A2:AB2000. I would like to add this code to the macro I have already created.
