I have no experience with VBA programming. I am currently trying to create a function that sums the cells that have a specified background color applied by conditional formatting.(ie red)
I have tried working off an example that was displayed below
http://www.cpearson.com/excel/CFColors.htm
i want cell a1 to highlight if cells b1,c1,d1 are greater than 1.
View 2 Replies View Relatedcan i format the cells that i have made grey for this photo to automatic put : between numbers i put in like the time.example if i would put in a cell 1200 than it would be like 12:00 is that possible i have been through all the time formats in the format cells options but i always get this result ###.
the other ? is can i take the S away when i have formated cells to show kilograms so it would be like 550 kg instead of 550 kgs ?
I have h26 and h27 both can generate a number
if h26 is a number i need f26 to formate in dollors
if h27 is a number i need f26 to be general formate
in the cells h26 and h27 they will already be in that format how can i get them to go to f26 like that
******** ******************** ************************************************************************>Microsoft Excel - PROTOTYPE2207.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF26=
EFGH26s1*$1.0027***1Weekly Stats*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
is there a way to merge cells when conditional formating them
example if a1- team then it will be 2 cells
if a1 = player then it will be 1 cell?
i have a list of customers with various information, i would like to rank these customers 1 to 5 and change the colour of the cells dependant on there rank.
Conditional formatting allows me too do this but is limited to 3 formats.
I have Columns A-K filled with data and want the condition to be set on column B if Column B meets a requirement it will then colour that Row (A-K)
So for example Row 3:
B3 = the requirement to change colour to red so cells A3:K3 will turn red
B4 = the requirement to change colour to gree so cells A4:K4 will turn red
and this must go down to row 1000 or whatever it may be.
I have a single column of data that is, perhaps, 100 rows long.
I have ticks, crosses and 'N/A' in various cells within this column. There are no cells that are blank.
I want to count the number of ticks within the column and want to be able to say:
If the number of ticks is = 100% then it's green.
If the number is >75% and <100% then it's amber.
If the number <75% then it's red.
I also want to exclude the N/A cells from the % calculation.
I have used VBA to apply conditional formating to a range of cells. i.e if cell B14 <>"" then row 14 is pale blue untill cell V14 is populated with the time then it removes the formating. One criteria is that if Q14(21/08/06 20:00)>NOW(AA2) and < NOW+1(AA3) then Row turns green(i.e. is due in the next 24 hrs).
Problem is that this formula is applied after a field is updated. When 21/08/06 20:00 comes and goes the row remains green unless I update one of the fields along that row. What would I have to do to make the formating change back automatically when the critera is no longer being met. Here is my codethere are 2 other if statements similar to this with in this code but this is the only part that shouldn't require any user input for the formatting to occour)
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B13:AP162"
On Error Goto ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Me.Cells(.Row, "AP").Value = "YES" And _
Me.Cells(.Row, "Y").Value <> "" Then
Me.Cells(.Row, "B").Resize(, 30).Interior.ColorIndex = 43 'mad green
Else
End If
End With..................
In one tab, I have a huge data set as follows:
account --- account(1) --- value
111111 --- b5 --- 123456
111111 --- a2 --- 32156
111111 --- c5 --- 31221
111111 --- e7 --- 1121312
222222 --- b5 --- 5464
222222 --- c1 --- 887895
333333 --- d4 --- 45454646
in another tab, I want to be able to manually input a value, in this case account, (1111 for example) and have it return all the values in account(1) (in this case b5, a2, c5, e7). Once I have that, I can do a vlookup to get the "value" which is what i need, but I just can't quite seem to get it to do the first part - I input account and it returns all account(1) values.
I've got a column with numbers. some of the numbers have the following signs/symbols proceeding the value in the same cell: ',ft,#,ea,each. I want to have these removed by a formula so that the numerical values are alone.
View 3 Replies View RelatedI have been using Conditional Formating to change the backgound color of the cells, this works fine. However it cannot be used with a userform text box. There I believe that the use of If Then Else statements will sort out the problem.
I have developed three conditions:
1) D10 less than E5 result True
2) D10 less than F5 result True
3) D10>E5 result False
using that result background color to turn green
Next condition
1) D10 less than E5 result False
2) D10 less than F5 result True
3) D10>E5 result True
using that result background color to turn yellow
Next condition
1) D10 less than E5 result False
2) D10 less than F5 result False
3) D10>E5 result False
using that result background color to turn red
What I cant seem to do is combine the three instances above so that the cell back color changes.
This is so easily done using the built in conditional format function but I'm have a difficulty understanding how to make this work.
I have a spreadsheet with conditional formatting that highlights my data range in a row red when their is no data entered in cell (E2) with this formula =COUNTIF($E2,"")>0 Now my question is...How can I make the highlighting go away if I enter information into cell (C2)? I added another condtion that said =COUNTIF($C2,"")
View 9 Replies View RelatedI have trouble formatting certain cells to show $- instead of $0, and there is cell showing ($0.00) instead of $0.00. See attached file.
View 2 Replies View RelatedI have created a drop down box that allows me to choose multiple different Text Options. Example: Cell B4 has dropdown that lets me choose the Text Options, "House", "Car" and "Truck".
Next, I have 5 or so other columns underneath with their own text. What I want to do is have different cells highlight themselves depending on what is in Cell B4.
Example, if "House" is selected in B4, I want cells B7 and B9 to highlight. If "Car" is selected, I want cells B7, B8, and B10 to highlight. If "Truck" is highlighted, I want cells B9-B11 to highlight. Is this possible to do? I've tried using If/Then statements, but those don't seem to work for this type of thing.
I'm trying to get it so that a cell becomes automatically highlighted if 2 other cells conditions are met. For example, I want cell A5 to become highlighted if BOTH cell B1=Loan 1 AND cell B5=Yes. Here is a mockup of what I'm talking about incase it makes it easier to understand. I'm using Excel 2007.
View 3 Replies View RelatedI have searched the forum and the 12 pages of results that had the words conditional clumns, but could not find the answer. I am new here so I don't want to ask a question that has been asnwered before. If I missed it I apologize.
I would like to create a spreadsheet that would "automatically" create the proper number of coumns or rows to satisfy a certain set of data. This may be better explained by way of example.
I do financial analysis of real estate. This real estate is say 100 lots today and these lots are going to sell out evenly over a 10 quarter period, or 10 sales per quarter. Simple enough to set up and show the proper cash flow. But lets say I wanted to chnage the sell out period to 8 quarters. Today I would have to manually delete the last two columns. Further let's say I want to run an analysis that has a sell out over 13 quarters. Then I need to add back in the proper number of columns and make sure I correctly copy over the formulas etc.
I'd like a way to have the spreadsheet automatically determine the proper number of columns (or rows) based on a formula that is part of the input data.
I have a macro which autofilters a range of cells.
I can count the number of visible rows by using the following code
Code:
lcountActive = Range("BC34:BC" & x).SpecialCells(xlCellTypeVisible).Count
What I would like to do is both calculate the Average value of the visible cells and the Sum as well. I have tried to adapt the code I have but my knowledge is just not good enough to do this.
I run an online store where all the product details are handled by a .CSV file and naturally all the information has to be exact or it won't work at all especially UPC codes and product codes. However whenever I open the .CSV file to edit anything it auto formats several bit of information including several UPCs and product codes that begin with one or more 0's. This among many other problem I have had are all caused by excel's auto formating "tool" and I find it of absolutely no use. Is there any way at all to disable this "tool" so it will stop messing with my data every time I need to make a product change? (I have tried using the '0 trick but when I save the file and open it again later all the '0s are gone again)
I am running Excel 2007 on Windows Vista SP1
I have an excel document that has one columb (I) of cells that changes color depending on a specific date (columb h) . The cells that have conditional formating to change the row color based on where it is ( =MOD(ROW(),2)=1), stays blank and the proper color until columb A is filled in. The others show up red. How would I code/format the lines that aren't included in the conditional formating to always show white when no value is entered in columb a.
View 9 Replies View RelatedI need to replace a "." with a "," to stay in the right format, by using VBA.
if i make a macro to do it, it will work for almost all cells but for a few it doens't work!
Then, if i select the cell with mouse and replace "." with a "," it works normaly.
My question is why it doesn't work for a few fields if all the data have the same format?
I have excel 2000 and conditional formating has only 3 options. Is it possible to use a formula so that if a critria matches, the cell chagnes colour?
View 9 Replies View Relatedi have the following table.
A1A2A3A4116747001169470011706300117075011707501170710511707100
the formatting which is in A1 I want that in A4 I will automatically appear like above (at the moment i do this formating manaually)
I have a column with addresses and I need to separate it into columns, the main problem is that sometimes the listing has house number sometimes it does not some time it has town but, some time it does not and the post code is not separated from the county - if no house number then that column is to remain empty.
Eg; (rows 1-2-3)
Bull Corner, Rainham Road South, Dagenham, Essex RM10 8YT
Bernard Road, Romford, Essex RM7 0HX
420, Eastern Avenue, Gants Hill, Ilford, Essex IG2 6NS
Need to enter them into the right columns:
House number - Road - Town - County - Post Code
how to use the conditional format tool, but now the company i designed the spreadsheet for, wants me to implement a change. I'm attacking a copy of the spreadsheet so you can see the problem more easily. Now what I've been asked todo is the following.
1. When the stock quantity (Column H) goes below the re-order level (Column K), they want the entire row to change font colour from blue to red. Now I know how i can change the colour of one cell, like ive done in the example, but I'm not sure how to change the entire row colour. If this can be done with conditioning formating then great, but if not then i'm stuck, and relying on your generosity in helping me out.
i have 2 columns the first is the transaction number and second column is the description
i want to make that all even transaction number will highlight the whole row... how do i make that with conditional formatting? or are there other alternatives?
I am using this formula in a Conditional Formatting
=VLOOKUP(G1,Dies5PE,1)>0
Where "Dies5PE" = a named range
it doesnt work.. if I change the"G1" to a "$G1", everything in the range changes to the conditional format.
I have a large X-Y-axis table with about 200 entries in it. There are 6 different entries possible. I now want to give each possible entry in this table a color for making it easier to read.
I could write a formula in conditional formating, so that it works for 3 colors. The problem is now that i can make this only for 3 different colors in conditional formating of excel. Is there a possibility for making this for 6 colors?
I have two columns from which criteria must be satisifed. column A denotes gender (m or f) and column B denotes a number of points. I want to sum those males who achieve >4 in one cell and those females who achieve >4 in another cell.
View 3 Replies View RelatedI want to set up formula in conditional formating so that when:
$AF6 = "CA" the color is red
or
if $AF6 = ("GA","ME","PR") then the color is blue
what I came up with is Condition 1
= ($AF6 ="ca") [which will turn the cell red]
Condition 2
="OR($AF6={""GA"",""ME"",""PR""}, "")" [which doesn't do anything]
is there a formula that i can use to check if a text in a cell is in bold?
i.e something like :
=isblock(A2)
to return true if the text in A2 is in bold