I have a table that I would like to format visually to enhance it. I know about conditional formatting, but this doesn't seem capable of what I want to do.
Basically, I have a table, and the contents of it range from A6:H59. I want the formatting to affect an entire row based on two factors.
As an example, we shall use row 6.
If cell A6 isn't blank, and cell H6 IS blank, then I would like the entire row from A6:H6 to be coloured in Amber.
If cell A6 isn't blank, and cell H6 isn't blank, then I would like the entire row from A6:H6 to be coloured in Green.
Of course, I want this formatting to the true of all rows in the sheet, but do I need to have a conditional formatting code for each row? Does conditional formatting even go into things this deep?
I have contemplated using VBA, which I know is more than capable. There was an example given in another thread, but my VBA is poor as I haven't programmed a bean in years, and I don't know where to even start. Below is the example code take from another thread similar to this (I didn't want to hijack another man's problem).
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) ..........................
I need to add a space in front of a string of numbers/letters, but it still doesn't seem to match what's in the lookup range. Granted, i get the lookup range from HQ, so there may be a formatting issue.
I have 1000's of columns of data to go through, I just need to visually inspect the data and then delete any column containing irregular data. At the moment; I select a section of the first column of data and insert a column chart for that data. I then need to change the source of the chart data to that of the next column, to do this I click on the data within the chart and it will show the data on the worksheet that I have selected by surrounding it in a blue box, I then click on this box and drag it to the next column of data. Clicking on the blue box (which sometimes I miss, and I then have to go back to the chart to activate it again) and dragging it to the next column is getting annoying, especially as I have sooooo much data to go through. Does anyone know if there is a shortcut key for this, or how I could write a macro to do this?
i've reached a point where i would like to learn more about class modules but i have no resources. When i create a Class object can i visually see it? i want to know as i'm looking to use them within userforms if possible
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
I'm trying to analyse all of my bank statenments to see where all my money went!! I've got online banking so I thought it would be a relatively easy process of copying and pasting each month into excel.
Well, that in itself was easy, the problem is with the formatting of the financial numbers. Excel doesn't seem to recognise them as numbers, so at the moment I can't do any manipulation with the numbers. I have tried everything such as:
Format Cells > Number > Number and Currency Copy and Paste Special > Values only Copying into Notepad and back into Excel
Even if it appears to have changed the numbers to 'number' or 'currency' formatting by right justifying the numbers, if I try to add up the numbers using the SUM function, it doesn't want to know.
For some reason nothing seems to work - what am I missing?
For an example, I have attached a snippet of the spreadsheet with my starbucks transactions.
Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:
if(A1=blue background,"Yes","No")
or
if(A1=red text,"Yes","No")
etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.
Any VBA that I can put into a macro that will convert conditional formatting into fixed formatting..? So when the cell contents/formulas are deleted the formatting remains. Assume that the range I want to convert is A1:D200...
Does anyone have a workaround for the following error "Excel Could Not Save All the Data and Formatting" Error Message (http://support.microsoft.com/kb/215783). I am applying validation to many, many cells
I wasn't sure how to properly title this post. I actually have a couple of questions. The main one is on how I can code my VBA so that for the range A11:N80 I have specific conditional formatting code, and then for O11:AB80 I have different conditional formatting code, and so on for 12 sections.
The conditional formatting reads column L (in the first section) for one of 6 different words and depending on which word is read, it fills the row from A to N with the corresponding color. I have this working, but I do not know how to do the same with with section O11:AB80 and so on. The actual formatting within each section will be the same but the data is different so different lines will be filled in. Here's my code so far:
I have a userform set up to create a new row on a worksheet and enter data that was filled out on the work sheet. Some of the data comes from text boxes which put in name, rate, division, received date, and loss date. The other info is taken from checkboxes and designed to put either an X or N/A, or just leave a column blank. Basically, what I want to do is figure out, when I make a new row, how to format that row to have borders around each cell, make columns 6-29 bold, and the cells in columns 6-29 colored with a gray background. If you need a dummy worksheet to better describe what I'm looking for, let me know. I haven't made one up yet.
The following image shows the problem I am having with my formatting in excel. There's nothing in the cell featured to make the cell length longer, but the combo box still does what is shown in the image. any idea how to prevent this? it looks messy for an end user!
I've created a database template that my partner and I will have on our laptops to use in the field. It is viewed in Page Layout.
When it is on my laptop, I show 30 rows on page one of the first worksheet. But on my partner's laptop it shows 29 rows. This messes up some of the formatting.
It isn't because he has changed anything. I sent my copy via email and a flash drive and opened it myself, and the problem still exists.
I am using the VLookup function with two seperate sheets. The first sheet is what I have created. The second sheet is one that was given to me.
I know how to build the format as I have done it numerous times in the past. This one only works if I copy the data into notepad, then copy it back into excel. Then it will work. I have looked at the formatting and I cannot see any differences.
I have a workbook that is used by others and has been working reasonably well for several years. The users are youngsters who tend to play with it when a bit bored but I usually manage to put things right. They seem to have done something special this time.
The formatting for dates, time and money has gone and I cannot correct it. I have tried the usual formatting of cells, painting formats from other sheets, copying and pasting formats all to no avail. The whole sheet is effected so that new entries appear the same way although the correct format appears in the formula bar.
I don't know if this is relevant but the sheet relies on VBA form inputs. The code is password protected and only I know the password.
The PC is not connect to the internet or a network so I have ruled out a virus. (As far as I know, no floppies or USB sticks have been used on it either.)
I have a large list composed of three columns, First name, Last name, and Email address. For the most part names and emails match up perfectly but there are an unknown number of names that do not match their email address. Here’s the hard part, email addresses are in different formats for example:
some are firstname.lastname@somewhere.com but others can be lastname.firstname@somewhere.com or some email addresses are first name initial then full last name with no dot separator @somewhere.com and even turned around like Last name initial then first name. For the most part this is the only four ways the emails are configured but all email addresses have some variation of last and first name in them.
How can I find the number of email addresses that do not match their names?
I have a hard time when it comes to "If then" statements and conditional formatting. I have some data that I am collecting and entering into a spreadsheet for a couple different companies. Once I receive paperwork from those companies for the data collected I mark the data with a blue colored text. I would like to have a second sheet that generates what companies have not sent me their paperwork. "If the data is black text, then generate <name of company> on second sheet." The list is small right now, but as the project continues it will grow very large and it will become difficult to track. I will attach a copy of the spreadsheet for you to see the situation. I would like to learn how to do these types of formulas, is there a tutorial geared towards these types of situations?
i have an application that can export tables to Excel. But in Excel, the data is only recognized as text, and not as or numbers. A typical column looks like this:
$300,40 $500,23 ($350,00) etc.
An amount in brackets signify a negative amount. Is there an easy way how i can convert this cells into numbers i can work with in excel? I have a similar problem with dates, times and cells with both date and time in them.
I am doing a golf predictor where I am using all 3 sheets in a spredsheet.
In sheet 1 I have the teams of players my friends have picked.
Sheet 2 I have the prgressive scores for each team done on a weekly basis.
Sheet 3 I have the names of 56 golfers and the points each can win for finishing anywhere between 1st and 22nd in a competition.
What I would like to know is there anyway I can replace the values 1st to 22nd with points (i.e., 1st = 400 2nd = 340 3rd =310 and so on down to 22nd) also is there a way I can transfer values between sheets.
i have a tab delimited file that desperately needs cleaning up.
i would like this:
word1 def1
word1 def2
word1 def3
word2 def1a
word2 def1b
to look like this
word1 def1, def2, def3.
word2 def1, def2.
my problem is this dictionary goes on and on, and i would just like a simple way to find these duplicate entries and consolidate them into the form i showed above
I have the following code which prints the number of time steps required and displays it as t0, t1, t2 etc how can I make all the numbers subscript? i.e until the number of time steps = the total time.
