I 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 am trying to create conditional formating of a cell based on the value of another cell whish is linked to another sheet. I an using the "The formula is =$AA$30=1" where the cell AA30 is the cell linked to the other sheet. the problem is I have to do thiss for approx 200 cells ie =$AA$30=1, =$AB$30=1, =$AA$31=1, =$AB$31=1 --- =$AZ$42=1. Do I have to enter the formulea for each cell or is there a way to automate this?
View 2 Replies View RelatedI 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?
Is it possible to return NA() to cell A10 if A10>A9, without adding a 3rd cell ??
I have an AutoFilter list of 14,000 rows by 14 columns, and the cells have some specific formating: fill color, font,
protection status, wrap, etc....
There are an additional 7 columns of formulas to the left of the filtered range.
The strange thing is----
-WITH the formating, trying to Unfilter the list takes 2 min, via a manually activated Data>Filter>ShowAll OR via a macro run of 'ActiveSheet.ShowAllData' .
(In an attempt to optimize speed, the VBA macro sets calculation to manual before the 'ActiveSheet.ShowAllData' and screen updating set to false.)
-WITHOUT the cell formating (eg. by doing Edit>Clear>Formats), the ShowAll takes about 3 sec.
Does anyone have experience or an explanation for this?
Why should the Formating affect Filtering so much?
Options for improving speed of autofilter?
I don't know if, or why it would be a factor, but note that I am using Dynamic Named Range and VBA to expand/contract the formulas
and formating to size of the list/table. Although this is not done during the filtering use.
Here is the dynamic formating code
Sub DynFmt_List()
Application. ScreenUpdating = False
With Application
.Calculation = xlManual
End With
I am wondering if some strange 'artifact' of manipulating the formating is becoming a factor?
I am trying to have conditional formatting of cells apply only if another cell is blank in 2007. A3 has a formatting of =MOD(ROW(),2)=1, then =AND (A3<>"",A3+15<=$G$1) to change the color based on date and finally =AND(A3<>"",A3+30<=$G$1) to again change the color again by date. What I would like to do is for the cells in columb A to change only if corresponding columb B cell is empty, without using macros if possible.
View 2 Replies View RelatedIs it possible to do a conditional formating based on a value being entered into the first cell of a row?
Basically when a date is entered into a cell in column A, I want the cells in that same row (A-J) to have a border, even if B-J do not have any value entered into them.
I need the formula that will color the cell if value is Text= Sat or Sun
View 2 Replies View RelatedI am now wanting to do if the the cell has an active conditional format also highlight that cell. Currently if the cell has an active conditional format nothing happens
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static rngPrev As Range, PrevColor As Integer
Dim TempColor As Integer
TempColor = Target.Cells(1, 1).Interior.ColorIndex
If Not rngPrev Is Nothing Then rngPrev.Interior.ColorIndex = PrevColor
PrevColor = TempColor
With Target.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
Set rngPrev = Target
End Sub
i am use exl2000 and i made a workbook with password protection. 2 sheets are in this workbook. one sheet is password protected and second is not protected. i want to change the cell color and font color (Cell is locked) in protected sheet. i want to give only content protection and user cannot delete the text but he will change the color of text.
View 6 Replies View Relatedhow I could have a range of B15-F15 to place Today's System-date in the Cell: A1, anytime anything is entered in that range?
View 9 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 have used Format Cells -> Number -> Category: Custom
and inserted Type: 00
This makes it so that:
If "1" is entered into the cell, it will display "01"
If "12" is entered into the cell, it will display "12"
This is exactly what I want it to do but here is the thing: It displays "01" but the cell value is still just "1". I want to be able to use the cell value in another cell where I have typed in:
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 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.
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 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
i have a list of about 50,000 songs
my layout is like so...
artist1 song1 disc#
artist1 song2 disc#
artist1 song3 disc#
artist2 song1... you get it.
what i want to do is reformat it so it looks like this
artist1
song1 disc#
song2 disc#
song3 disc#
artist2
song1 disc#
so the artist and song could be in the same column with the song indented or if someone knows another way that works too.
I have a spreadsheet
B4:B193 - place
D4:D193 - value
F4:F193 - name
H4:H193 - place
J4:J193 - value
L4:L193 - name
N9:N93 - place
P9:P93 - value
Q9:Q93 - name
What i am looking for is this:
If value in cell in column D is >=90 then relevant cell in column B, D and F background green
If value in cell in column D is less =80 then relevant cell in column B, D, and F background dark blue
If value in cell in column D is less =70 then relevant cell in column B, D, and F background light blue
If value in cell in column D is less =50 then relevant cell in column B, D, and F background orange
If value in cell in column D is less
This thread:
http://www.mrexcel.com/forum/showthr...itional+format
Asks almost exactly the question I have, but I can't make heads or tails out of the answers, I'm guessing becasue I have Excel 2003 and not 2007 as mentioned in the replies.
In column "A" I have a number; column "B" the formula =A1 copied on down and in column "C" either a 1 or 2 or a 3.
If the number in column "C" is a 1 then the font in the same cell in Column "B" should be black, 2 Blue and 3 Red.
I'm thinking this should be very simple, but so far it's not turning out that way. )-:
I would like to format a cell so when i enter a six digit number, such as 123456 it would appear starting with a QW then the year, month and day in the format of yymmdd and them the six digit number so it would appear as QWYYMMDD123456, or since today is 01/09/09 is would be QW090109123456
I would like the yymmdd field to be taken from the main location where the date is entered and the QW can be in another field so when I go to another blank field and enter a six digit number, for example 123456 it will be changed to QW090108123456. I hope you can follow this and that I explained it in a matter that is understood.