Posts by umlaut

Re: Color Range Based On Cell Value

Hi, (thanks for the reply kneejerk), from various research I have almost got a "cleaner" solution:

Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Count > 1 Then Exit Sub 'Limit Target to one cell
    Dim icolor As Integer
    Dim c As Integer
    Dim rang1 As Range
    
          
    If Not Intersect(Target, Range("C1:C20")) Is Nothing Then
        Select Case Target
        Case "R": icolor = 3 'Red
        Case "r": icolor = 3 'Red
        Case "Y": icolor = 6 'yellow
        Case "y": icolor = 6 'yellow
        Case "G": icolor = 4 'green
        Case "g": icolor = 4 'green
        Case "B": icolor = 5 'Blue
        Case "b": icolor = 5 'blue
        Case Else: icolor = xlNone
        End Select
        
        Target = UCase(Target)
        Target.Interior.ColorIndex = icolor
        'rang1(Target.Column.Offset(0, -2), Target.Column.Offset(0, 9)).Interior.ColorIndex = icolor
    End If
     
End Sub

but you will see i have stumbled on the commented line (rang1) can anyone get me over the last hurdle? I can colour the target cell but can't extend to the range I need..

many thanks..

Hi guys,
I have data in cols A-L (1000+ rows)
In column C for each row I have any of the following letters M/P/I/D/L ( or blank) - if blank, no cell colouring required.
I need the cell background in cols A-L on each row to be based on the following
Lets use row 1 as an example
Cell C1 = "M" - background color for A1-L1 = Blue
or other colours as follows:
"M" = background color = Blue
"P" = green
"I" = orange
"D" = red
"L" = yellow

I need this to be in VBA and updated on the fly..

I have found a lot on the forum about conditional formatting in VBA, but not quite what I need ...can you guru's help with some code?

I will have more values and colours to add later, but if I can get teh above I will be able to customise quite happily

Thanks guys !!

Re: Import CSV without "special" characters

That's brilliant JitUK - thank you so much.

I couldn't find out whether this was a change in source data, or something that happenned when I upgraded to Office 2003 with SP2, but doesn't matter now - and another great little utility in the locker...

cheers..Rod

Hi - I have been using some vba to import data from a csv file and then do some processing. Been working well for ages, then suddenly today, when importing the csv, some "special characters appeared.

the normal format for the name field within the csv which imports into column A of the spreadsheet is :

Bloggs F.
Smith A.B. etc

However, today, two little "squares" with darker lines top and bottom have appeared between the surname and the initials. (Bloggs * * F.) I can manually delete them, but I can't do a find and replace because the symbols won't cut and paste. They look like "control charcters" of some kind. I don't know whether the guys who gives me the source data has changed something, or whether it's at my end. I can't cut and paste into thsi post either

I have installed Service Pack 2 since I last ran the code - don't know if this has changed something? (I'm going to test on my work machine tomorrow which hasn't got SP2 on)

This is throwing my code out, because I match the names and now there are no matches because these funny characters are in there.

Any offers anyone?

Re: Drop Down List Validation & Manipulation

Hi Batman

That looks pretty cool to me - I've given it a go and seems to be working well - I can easily extend it to the newly promoted teams into the premiership.

I'm reasonably good at using and amending code, but not good at "creating" it - and I've never used private function before, so you've given me some new strings to my bow there as well.

Many thanks for your help

cheers..Rod

Re: Drop Down List Validation & Manipulation

Hi Batman - many thanks for your interest

The numbers on the right are a legacy to when the program that actually calculated weekly points etc was originally written in Pascal (not by me) and was a quick way of getting the entries into the database - what used to happen was that when people submitted their entry, the guy who ran it used to trawl thru all then entry forms and write down the team number, and then use that to enter peoples selections !!

This entry form that I wrote for him in excel at least gave him the numbers and cut down his manual work !!

No reason why we couldn't have an additional column having a flag for the premiership top 3 teams from the previous season and the newly promoted teams in the team list sheet.

As long as we genarate the entry form "as is" we have a blank canvas really -the guy asked me if I could do this tweak re data validation on the entry form - but sadly it beat me ! - that's why I asked you guys !!

thanks for your help - and look forward to seeing what you com up with !!

cheers..Rod

Hi
We have an entry form for our annual football comp, which has drop down lists for team entry. We'd like to enhance it for next year, so that the drop down lists have some intelligence !

Basically you have to select 3 teams at the top & 3 teams at the bottom of each division, but we have no validation to stop you selecting the same team twice. So that's the first challenge.

Secondly, last year everyone chose Arsenal / Man U & Chelsea at the top, and the newly promoted teams at the bottom, so next year we only want to allow you to choose one of the previous years top 3, and one of the newly promoted clubs, thus forcing a wider choice of teams.

The sheet is attached (ignore the fact that the teams aren't in the correct divisions on the data sheet, it's a skeleton from a few years ago)

Hope you can help chaps..

cheers...Rod

Hi - I have a userform configured with a number of command buttons and check boxes.
Each of the command buttons fires off some VBA, running various updates and utils on the data within the spreadsheet - and I have the hover-over help for each of the buttons. This works really well and has been in use for a year.

What I would like to do is put a set of instructions about the overall process for when sombody else has to run the process -for instance

1 Run utility 1 by pressing "xxx" button - then review the data, make the following changes then run utility 2 by pressing "yyy" button.

2) On completion of the above, run util1ty 3, by pressing "zzz" button, and then if the data is valid, print the workbook using the "printx" button.

3) On completion and validation, save the new workbook to xxx directory, with a hardcopy filed in the xxx room for audit puposes.

You get the drift, it's really an overall procedure.

I thought about changing the userform to be multipage, having the controls on one page, and the instructions on the other - and using labels for each of the instructions, but in the label, I don't seem able to format the text, can't make the first line bold / underline etc

So...couple of questions - any offers on a better or alternative way to present the set of instructions, and any alternatives to using the "label" control on the userform to make it look "prettier"?

many thanks..Rod

Re: Total and subtotal selected rows

Derk - you've done it again - how easy was that !!

many thanks..Rod

:thanx:

Hi,

Hope you guys can help with this..

my data is in 7 row blocks, many columns - at the end of each block I have a manual subtotal =SUBTOTAL(9,E5,E10).
the last row has a total in - I have been using
=E11+E19+E27+E35+E43+E51+E59

I have VBA button to add a new block of rows, but trouble is, if I add an extra block , I have to amend the total formula to be
=E11+E19+E27+E35+E43+E51+E59+E67 and then cut and paste that formula across many columns - which is a pain - the columns are not contiguous

how can I get the total row to just pick out the subtotals ( or ignore the subtotals) or automatically update the formulae?

many thanks...cheers..Rod

Re: Vlookup 2 ranges

Nice one Michael - and thanks for the explanation at the end - it made it a lot clearer - once I start nesting functions my head tends to hurt !!

cheers..Rod

Re: Vlookup 2 ranges

Hi guys - trawling thru the forum and I have the same query here - BUT I need to search 3 ranges and need to return a zero instead of an N/A if no match....

Any offers?

many thanks..Rod

Thanks guys - been out of office all week, but I've come back and you've done it again - many thanks...Rod

Hi all,

This should be simple, but can't seem to find a suitable formula.

If I put the name of a month in cell A1 for instance (June)

I want to have B1 be 01/06/2004
and B2 be 30/06/2004

(UK date format obviously)

If I change A1 to be July, I want July start / end dates in there....

what formula should I use?

any offers gratefully recieved...

thanks.....Rod

Top Man Dave - just one additional question, if I want to do this for a named range rather than an entire column, how could I do that?

cheers..Rod

Hi - I reopened this one because I think the answer is going to be similar, but just can't get it to work for me.

I want to select a number of items from a list of 500. At the moment, I put a "1" in column A in the cell adjacent to the item I want to select in column B.

Then if I want to "deselect", I put 0 - I have some functions / vlookups to display the list of items I have selected - like a shopping list.

Is there a way I can change the "selector" cell value between "0" and "1" just by clicking on the cell?

example

A B
---------------
0 ¦ item1
1 ¦ item 2
0 ¦ item 3
1 ¦ item 4
--------------

In the above example, I have "selected" item 2 & item 4 - If I wanted to de-select item 2, I enter 0 in A2.

I want to select / deselect just by clicking the cell, and have it's value alternate between "1" and "0" ( or possible "blank" which may look tidier.

Hope this makes sense..

cheers..Rod

Thanks xlite - that's exactly what I needed - your code worked straight out of the box...!!

Dave H - thanks also for your links - I'll have a play with those as well

many thanks to both....top guys !!

Hi

I have got a work rota spreadsheet
If you are at work, there is a 1 in the cell
Monday thru Friday cells are white background (no fill)
Saturdays are green fill
Sundays Orange fill
Bank Hols Yellow

Each person has a separate column, so I can sum the column and know that for a given month they are at work "x" days

I'd like to break it down so that under each column it shows "X" Mon-Fri , "x" Saturday and "x" Sunday and "x" bank Hols

So in a nutshell, how do I sum contents of yellow cells , sum contents of Orange cells etc

any ideas anyone?

formula or VBA don't mind...

thanks in advance for any suggestions..

Yep - first thing I checked, because it seemed an obvious potential cause - but all cells are fully loaded with borders !!

Sorry to resurrect this post, but I have a similar problem.

I am printing a matrix of golf scores - there are 114 players and I have their scores for each hole, thus, this print runs to abouut 4 pages in landscape.

For some reason, even though borders are on for all cells, the LAST row on each page doesn't have its bottom border printed. However the last page DOES have its bottom border printed.

No real damage done, however it is slightly annoying as each fo the first 3 pages look as if they are "unfinished".

NB - All pages are well within their maximum print range for A4 paper on my Epson C62

Any ideas?