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?
I am doing a spread sheet for to enter the times that i do in a rally.
So if i post a time of 00:49.3 and the target time is 03:54.3 i want the cell to turn red, or if the set time is less then the target time it should be green.
I found the code below to do almost exactly what i want. However, i would like to know if it can be modified to only affect a specific cell range. In my case, the cell range is B9:AF37.
Option Compare Text 'A=a, B=b, ... Z=z Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range Dim Rng1 As Range
On Error Resume Next Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) On Error Goto 0 If Rng1 Is Nothing Then Set Rng1 = Range(Target.Address) Else Set Rng1 = Union(Range(Target.Address), Rng1) End If For Each Cell In Rng1
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?
I am tyring to do conditional formatting whereby if anything is entered in a cell i want the colour to change to say yellow and if nothing is entered just to leave blank.
Not sure if I can do this with a formula or not. looking to change the value of a cell when using a column. Im looking to have the cell to change each time after there is a new value entered in the column but only when a value is entered in new cell of the column.
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.....
Code:
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 have 5 cells in the same row (C20:G20) When i click on one of them i want it to either change the background of the selected cell or the background of the cell below it.
EG if i click on C20 either C20 or D20 the highlight black and change the text colour to white, or just change the background to grey.
i thought it might be something like Target.Offset(1, 0).BGColor = 6
i tried this>
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells = C20 Then Target.Offset(1, 0).BGColor = 6 End If End Sub
Trying to have this embedded in the worksheet private sub change section. I want it to change the value of these ranges when the target has a value in it. I'm getting an error though:
If Target.Address = "$E$27" Then If Target.Value "" Then Range("I25:J26,P25:P26,R25:S26").Interior.ColorIndex = White Else Range("H25:V31").Interior.ColorIndex = 15 End If End If
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)
I need to have the color of cell A2 change when I enter an asterisk in cell A1 to denote a critical dimension on an inspection form.
I used conditional formatting to change cell A1 orange when an asterisk is entered. Is there a way to tell cell A2 to look and see if there is an asterisk in A1 and if so change it's color?
I need a script that changes a cell to say closed (uses a drop box) when a date is entered into the date column. I have attached a workbook with the basic options, obviously there will be a lot more columns and information in the original workbook and I will adding the code to at least 2 worksheets.
However to give a basic understanding if I have a drop box in column A with the options Active, Inactive and Closed. When I enter a date in Column B (headed Closed Date) I want Column A to change the option to Closed automatically.
New cases are entered on to the spreadsheet. The case remains open until a closure date is entered, at which point the case is closed. What I need is the following:
Cell A3 = should say "Open" if there is no value in cell Y3 Cell A3 = should say "Closed" if a date or any value is entered in cell Y3. Ideally it should only say "Closed" if a date is entered in format xx/xx/xx, but am flexible so that it says "Closed" if any data is input.
At the moment Cell A1 has a drop down list consisting of open and closed. I will remove this if it causes complication.
Is there any way to change the border color of a cell by running a subroutine? Here is my example:
Col 1 Col 2
1 2
3 4
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 looking to change a row colour if any type of data is entered into a specific cell.
Context: In column H entitled 'Start Date' when someone enters a date n any format into this box, I want the whole row to change colour to easily see what projects have started and what ones havent.
I want to change the colour in a cell depending on the sum of two other cells. ie A3=1,B3=1 A4=2,B4=1 A5=1,B5=0 A6=2,B6=4
So i want if A3+B3>2 THEN C3 TO HAVE GREEN COLOUR A4+B4>2 THEN C4 TO HAVE GREEN COLOUR A5+B5>2 THEN C5 TO HAVE GREEN COLOUR A6+B6>2 THEN C6 TO HAVE GREEN COLOUR
SO IN THE END ONLY C4 AND C6 WILL HAVE GREEN COLOUR.
Is there any formula to do so with conditional formatting?
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.
Is there any way to have a cell change its colour depending on whether a condition is met. E.G., can i have it as green if the result is a plus number and red if its a negative?
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 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.
Code: 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