I have the below code that changes the colour of a cell to green if the figure increases, and to red if it decreases.
The issue is that it works fine if you manually change the figure in A1 or A2 by entering the figure and hitting enter. If the cell figure changes in A1 by any other means such as RTD or say a value in C1 "=C1" then the colour change is not triggered.
Public oldval, myval
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A2")) Is Nothing Then
oldval = myval
If Target.Value > oldval Then
Range("A" & Target.Row).Interior.ColorIndex = 4
VBA macro to change the color of a cell automatically for a specific period of time -say 5 minutes, based on the value the subject cell holds at that time. The cell value is not manually entered but comes from a sub.
There will be hundreds of such cells so that the macro must be able to be repeated for other cells utilizing their individual cell values as well.
I am sending an email when the value of a cell changes, [url]
I am using the following code in my worksheet....
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Target.Cells.Count > 1 Then Exit Sub On Error GoTo EndMacro If Not Target.HasFormula Then Set rng = Target.Dependents If Not Intersect(Range("AE7"), rng) Is Nothing Then If Range("AE7").Value < 20 Then EmailOut 'MyMacroName End If End If
What I would like to do is increase the range that this applies to.
Range from AE7 to Range Y3:AE250.
I would like to Include the Range A3:A250 in the subject/body.
So If AE7 has changed, A7 (along with some text "This cell has changed, do X Y Z") would be somewhere within the email.
I dont have that great knowledge in Excel but i wanted to try and run a macro in an IF formula but realised that was not possible. I have now got a formula that gives a value in a cell when i want the whole row to turn to red, but im not sure how i can get a macro that will change the whole row to red when that cell shows a value. Can anyone help on this matter?
How I can change the background colour of cell C:2 depending on what other cell i click? Below a sample of the code im using Basically when i click B:2 i want C:2 to turn grey with the text on top.... Then when i click B:4 i want C:2 to turn Red.....
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Select Case Target.Address Case Is = "$B$2" Range("D2").Value = "No action needed connector is down" 'change this to suit Case Is = "$B$3"
I would like to have the colour of a cell change within a spreadsheet anytime that a change has been made. I have used the worksheet_change function to change any text that has changed in a cell, however I would like to have the actual cell colour change if someone deletes anything from that cell. I figured that I could use the worksheet_change function for that as well however it is not working.
I've found a great thread that answered part of my problem (Change colour of text on update of cell)
This code changes the colour of a cell when selected. With the application I'm trying to make I need this effect to only occur in a certain area say A10:D30
I'm trying to make a scheduling page that uses timebars to represent when people are working. I though if a user could click or highlight the hours and a time bar produced I could lookup to see what hour the timebar starts and ends and show the hours worked. I also need it to cycle through 5 different colours before returning to white.
Private Sub worksheet_change(ByVal target As Range)
Is there any way to change the border color of a cell by running a subroutine? Here is my example:
Col 1 Col 2
I have an existing table with data that is updated daily ... more rows are added. Currently, the cell border is black. I would like to run a macro such that the column header is shaded grey and the borders turn into a shade of gray. I am interested in the 35% grey.
I am trying to devise a service schedule that has the installation date in say cell D4 and I would like this to automatically place a date a year from there in to cell E4, then 4 weeks before date comes round i would like the text cell to turn yellow, then on & after the date to turn red. Is this even possible?
Is there any way to change the pattern color of a cell (or a group of non-adjacent cells), say from No Color into Red if the cell(s) protection is locked (or unlocked)? Seems to me that the Conditional Formatting cannot work this way.
How to a change a cell colour to say red in B6 if cell b12 = 1 and if e6 = 1 to change to green. I thought I might be able to use conditional formatiing but no. I had set the spread sheet to do a cell just for a condition representing a sum from another worksheet and it was working fine but i have to incorporate the two together and am stock.
I've seen examples of VBA that will colour autoshapes based on cell values. I have a map of the UK made up of autoshapes that have been edited to create a shape of each postcode area such as EX, GL, NR etc. Also I have a list of all these postcode areas and I have given them a zone number (1-7) depending where in the UK they are.
At the moment I have set the colours using the fill option so anything that is a particular zone is a certain colour (i.e. zone 2 is green).
What I would like to do is to set a zone to a colour, so the map will update all the zones with the right colours.
I need to be able to read the zone number that is assigned to a postcode area and change the relevant autoshape to the correct colour so that any postcode area can be one of 7 colours.
I can't attach a spreadsheet with my map and postcodes otherwise I would have.
Is there an easy way i can read the zone number of each postcode area in the list and update that autoshapes colour? Would the autshape need to have a name so it know which one to update or is this not possible?
Right I want to change a cell interior colour when another cell value is exceeded. I can do this but what I am after because I have over 56 colums and 390 rows I dont want to do this for each one is there a quicker way ? I have this so far which is for one cell only...
Private Sub Worksheet_SelectionChange(ByVal Target As Range) With Selection If [E8] > [E10] Then [E8].Interior.ColorIndex = 3 Selection.Interior.Pattern = xlSolid Else [E8].Interior.ColorIndex = 0 Selection.Interior.Pattern = xlSolid End If End With End Sub
I have found this code that, with changes, I can make work with the cells and columns I am using. What I cannot see is how to make more than one cell change colour rather than just the cell in column J.
How can I change the code below to change several cells in the row? I will be using cells in columns A:F.
Data will be entered into the rows from A2 to E2, in F2 a vlookup will enter one of the values, "High", "Medium" etc.
Each of these values has a colour assigned. The code below, with alterations, will be able to colour the cell in F2 depending on which value is entered. I need the whole row from A2 to F2 to be coloured.
Code: Private Sub Worksheet_Change(ByVal Target As Range)
Dim ChangedRange As Range Dim r As Long, iCol As Long
Set ChangedRange = Intersect(Target, Columns("F:H"))
I want to incoroprate into my Workbook a sheet with a list of phone numbers, that sheet can be added to and more numbers entered if necessary and will be called "DATA"
The questions is this. If one of those numbers in the "DATA" sheet is entered in any other sheet in the workbook can the cell that number has been written on (not the one on the data sheet) be made to change colour?