Colour Change Autoshape
Mar 29, 2007
I have been trying to make it so that an autoshape changes colour according to the value of a particular cell but every time I run the macro the shape is black and I cannot work out why. The code is -
Private Sub CommandButton1_Click()
Dim X As Integer
X = Range("H5").Value
With Sheets("Sheet 1").Shapes("Rectangle 1").Select
Select Case X
Case Is > 0:
Selection.ShapeRange.Fill.ForeColor.RGB = 2
Case Is < 0:
Selection.ShapeRange.Fill.ForeColor.RGB = 3
Case Else:
Selection.ShapeRange.Fill.ForeColor.RGB = 1
End Select
End With
UserForm2.Hide
End Sub
View 6 Replies
ADVERTISEMENT
Mar 21, 2012
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.
PcodeZoneDH3DL3DN2DT1DY2E4EC4EH5
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?
View 9 Replies
View Related
Oct 23, 2008
I am trying to automatically change the colour of an oval in worksheet1 to the colour of oval3 in worksheet 2.
Currently oval3 (Worksheet 2) has the a vba code that changes its colour when you click on it.
View 9 Replies
View Related
Mar 7, 2008
I am atempting to do some autoshape conditional formating. I am about above average when it comes to working with Excel, and I know little to some Visual Basic. Here are the two things I am attempting to accomplish.
1. I am attempting to use VBA to change the color of a Large Pyramid Autoshape called "JanPyramid". The thought behind this is, I wish to have the Pyramid show vbGreen when it is <= my target (which in this case is a user inputed cell called "RECTARGET", so instead of using a number in my VB code I would like to have it refer to this cell in E58) I want the Pyramid to show vbRed when the total Recordables (this number is generated by using a COUNTIF function, so it counts all entrys in the table that have REC entered for its status, in this case the cell is called "RECCOUNT" and is located in I54) is > my target, E58 "RECTARGET"
2. I have already sort of gotten this to work by using values instead of using cells. But it is still is not perfect. I would prefer to use the actual cells because my targets and the actual counts can be different depending on what happens during that month. Here is the code I have in so far, it is with the values instead of it being the cells because I do not know how to make it read the cells instead of values:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("RECCOUNT")) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Value > 0 And Target.Value <= 2 Then
ActiveSheet.Shapes("JanPyramid").Fill.BackColor.RGB = vbGreen
ElseIf Target.Value > 2 Then
ActiveSheet.Shapes("JanPyramid").Fill.ForeColor.RGB = vbRed
End If
End If
End If
End Sub
This code is a hybrid from this Ozgrid thread: Changing fill color of autoshapes based on cell numeric values.
View 5 Replies
View Related
Nov 29, 2006
condition: the cell in row K and row L can only have value 0 or 1
if cell in the column K & columb L = 1
then both cell is green colour
if cell in the column k column L
then both cell is red colour?
taking example if k1=L1 =1 then is both green colour
if k1 =1 and L1 = 0 then both are red colour
View 9 Replies
View Related
Oct 23, 2008
I have developed a profit loss sheet for the office I work for. What i need is to have either cells change colour or the numbers in the cell change colour depening on the value in the cell.
What I was thinking is if the number is over 15% of the sale price the cell or number should be green, if the number is under 15% it should be black, and if its below 0% it will be red.
View 4 Replies
View Related
Jan 16, 2009
I am using Excel 2007. One of my worksheets has a dark border around some cells. Instead of being blue the lines are black. I used the right click and format cells option but it does not work.
View 10 Replies
View Related
May 21, 2009
I have this code but is not working, i need to make an ajustment to this as well as at present it will only change colour to 1 cell but i need it to format the whole line, this is what i have so far
View 5 Replies
View Related
Aug 7, 2006
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?
View 9 Replies
View Related
Nov 15, 2006
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.
View 9 Replies
View Related
Nov 28, 2006
I'm looking for some code to change a cell's fill colour
eg. by selecting an individual cell in a range, say A1, the colour of a cell, say K1, in a corresponding range changes to yellow
or if I select multiple cells, say A1:A9, the colour of cells K1:K9 change to yellow
View 9 Replies
View Related
Jul 23, 2007
the VBA to change to colour of a shape based upon the value in a cell?
e.g. if cell "a1" = 10 then colour circle red
View 9 Replies
View Related
Oct 1, 2009
I want to change the font colour in row J7:J299 if there is perticular word say "Double" in row J.
View 9 Replies
View Related
Nov 8, 2009
How can you change the colour of a cell (A1) using that number
eg
1=red2=orange3=white4=light blue5=Blue12345
OR
Make the cell with numbers above 0 go red and all cells with numbers below 0 go blue
View 9 Replies
View Related
May 25, 2007
The company I work for has its own set of standard colours to be used in all documents. I've set the colours up in the colour palette using the below sub (x's are actually numbers):
Sub SetCompanyColours()
ActiveWorkbook.Colors(9) = RGB(x, x, x)
ActiveWorkbook.Colors(46) = RGB(x, x, x)
ActiveWorkbook.Colors(12) = RGB(x, x, x)
ActiveWorkbook.Colors(10) = RGB(x, x, x)
ActiveWorkbook.Colors(14) = RGB(x, x, x)
ActiveWorkbook.Colors(5) = RGB(x, x, x)
ActiveWorkbook.Colors(47) = RGB(x, x, x)
ActiveWorkbook.Colors(16) = RGB(x, x, x)
End Sub
I want to distribute this round to my workmates. Problem is that, if they set the colours in the workbook using the above sub, the workbook colours will be all wrong when clients open them on their computer. How do I get around this. I have thought about programmatically copying the sub into the open event of the activeworkbook. I've looked through this website, http://www.cpearson.com/excel/vbe.htm, but it doesn't explain how to copy code to the workbook events part of a vba project, only to a new module
View 3 Replies
View Related
Apr 1, 2013
I want to change the back ground colour of each row until G column when there is a text in the first cell. I have attached the excel that I want the background colour to be. Like suppose, F1 is the details of family 1, F2 is for family 2. I want each family to be coloured. The rows are not fixed for each family. If F1 details are coloured green, Fr is orange, F3 is green and F4 is orange again. And the number F1, F2, F3, F4 and so on are not ordered in ascending. They can be random. But I want the colour coding to be the same as what I have shown in the excel. I have so many rows like that. How do I do that using excel. I have tried conditional formatting. But it doesn't work.
View 2 Replies
View Related
Jun 12, 2014
I need to see easily if my cell is giving a value by using a SUM. This is so I can see if the SUM has been taken off or overridden by typing in a number.
View 4 Replies
View Related
Feb 8, 2008
If A1 contains 'good' then row A = red
I need an entire row to change colour if a particular cell in that row contains one particular word.
View 11 Replies
View Related
Oct 10, 2008
I'm creating a simple spreadsheet totalling units of alcohol up. Is it possible to change the colour of the fornt when different values are reached? For example if a total of under 8 units is reached this is displayed in green, if over 8 the figure is shown in red.
View 4 Replies
View Related
Feb 16, 2010
In cell A1 I have revenue projected & B1 I have actual revenue if the date in B1 is less than the date in A1, I would like B1 to change colour.
View 5 Replies
View Related
Mar 31, 2013
I am making a little Formula 1 Grand Prix spreadsheet, and would like to know how do i enter a number into a cell and the cell changes colour.
[Code] .....
So if i type when of these in a cell i want to to change to the colour corresponding to the chart.
View 2 Replies
View Related
Jul 31, 2014
I am using the following code to amend the colour of a cell when a name is entered in ROW J however, i need it to colour the row from A to O on the line the name is entered on.
So for example Dave is in j2 (j1 is a header) the line from A2 to o2 goes green.
I have tried a few things with interesting results but not get desired result.
[Code] ......
View 4 Replies
View Related
Mar 15, 2008
a button to run a macro but i don't like the standard plain grey buttons that excel offers. i saw a friend using one in his project but don't want to ask him how he did it as it would be considered copying. i will add an image to explain my specifications. there must be an easier way than having to design it and you vba to code the colour change.
View 11 Replies
View Related
Jul 17, 2009
I have the code below which fills a cell when doubleclicked. I would like to modify this to altenate between 3 different colours on each doubleclick.
View 2 Replies
View Related
Jan 4, 2010
I'm attempting to write a code that will change the colour of a series in a chart based on the name of each series. i.e. I have Roads, Water and Rail as three of my series names. can i somehow assign the colours yellow, blue and red to these series using macro code.
View 5 Replies
View Related
Mar 10, 2013
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"
[Code].....
View 6 Replies
View Related
Jan 22, 2007
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.
View 9 Replies
View Related
Apr 17, 2007
I dont understand how this event works.
I want a certain object to change colour when a certain cell = something.
View 9 Replies
View Related
Oct 18, 2007
=$CS18=INDIRECT("Client_Options!"&ADDRESS($I18,97))
I want to say when CS2= same value as CS2 on the client options sheet (on the row specified in I2 sheet 1) change the colour.
How should this be tweaked to do that? I'm not getting it!
=$CS2=INDIRECT("Client_Options!"&ADDRESS($I2,COLUMN()))
View 2 Replies
View Related
Nov 3, 2008
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
View 9 Replies
View Related
