Any question answered...

My macro is fugly.
It applies a conditional format to all the cells based on dates, and then for specific ranges it adds a specific cell format to that range based on the values in there.

Try this:

[code]

Sub awesome_code()

If (WorksheetFunction.CountBlank(Range("C3:CG3")) = 1 And Range("AS3").Value = "Method C" And Range("AT3").Value = "") Or (WorksheetFunction.CountBlank(Range("C3:CG3")) = 0) Then

Range("A1").Interior.ColorIndex = 0
Else
Range("A1").Interior.ColorIndex = 3

End If

End Sub

[/code]

Where A1 is the cell getting the colouring action

Cell is coloured red when conditions are not met, coloured "blank" when conditions are met

Tiswas: I'm new here. Do what to what?

Matt: ta.

Damo, the code in post 4164 is giving me the chills!

You shouldn't need to invoke conditional formatting from VBA - conditional formatting is a quick & easy way of writing the VBA yourself if you see what I mean.

I know. I look at it and think :
"Yes, my hovercraft IS full of eels. Thankyou for asking"

It's a recorded macro rather than a proper VBA solution.

My macro is fugly.
It applies a conditional format to all the cells based on dates, and then for specific ranges it adds a specific cell format to that range based on the values in there.

Tiswas: I'm new here. Do what to what?

Matt: ta.

I know. I look at it and think :
"Yes, my hovercraft IS full of eels. Thankyou for asking"

It's a recorded macro rather than a proper VBA solution.

Conditional format means that each conditionally formatted cell has a rule it uses to work out what colour it should be. Each time the data in the worksheet changes, the cell will recalculate the result of the data, and its colour may change.

That could be fine, but it sounds like you are trying to set the formulas twice (or more?!), working out what colour each cell should be by a process that has several steps and visits each cell more than once. This too would be fine if each time you visit a cell you just told it what colour to be (set the format directly from the macro), but instead you are telling it what rule to use to choose its colour. and each time you tell it a new rule it forgets all the rules it was told before, and you get weird results. If the last step of your process doesn't set rules for every cell then things get even weirder as the cells get left with a patchwork of different rules from different stages of the process.

Either set the colours directly step by step, and you will have to re-run the macro each time you want the colours updated.

Or set just one huge formula per cell that combines all the things that cell has to think about to choose its colour.

Or I'd do something like: make another worksheet full of formulas: some to check the individual conditions the data have to obey, some to combine the results of those checks to work out a result for each potentially coloured cell. On the worksheet with the data i'd set really simple conditional format rules that just read the answer from the formula worksheet and set the colour.
edit-----------
Urgh: actually this is wrong, conditional formatting is more twisted than i remembered, and you should be able to give each cell up to 3 conditions to check. And now i can't see why any of my expressions wouldn't work. The example you posted with your version of mattty's expression doesn't work because he was using it in the opposite sense in his If statement, so you need to wrap it in a NOT() to make it do what you want. Some study of boolean algebra may be helpful. I got from my first expression to my third by knowing that NOT(AND(A,B))=OR(NOT(A),NOT(B)) (one of DeMorgan's i think).

I'm confused...

Range("b3:B" & LastRow - 1).Select
Selection.FormatConditions.Delete

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"**=IF(AND(ISBLANK(CH3)),(CQ3>=CF3+365))**"
Selection.FormatConditions(1).Interior.ColorIndex = 3

Are you aware that there are two languages mixed in together here?

Most of it is macro language (visual basic), which is a procedural language - the macro works step by step through it obeying the instructions. It uses the dot notation as addresses to get hold of bits of the worksheet and do relevant things to them.

The bit in bold it excel spreadsheet formula language. Spreadsheets are magic, not procedural: all formulae are continuously evaluated. (Behind the scenes they do a lot of bookkeeping to work out which formulae a data change can affect, and ripple the re-calculation out from the change to only those formulae and only when the change happens). It is also a very regular language: all the functions come in prefix form, i.e.: AND(condition1, condition2, ...), FUNCTION(list of inputs to function). Unlike VB which does things like 'condition1 & condition2' where the AND function is in the middle.

So far, I've only been able to add three conditional formats.

There's one "toy town" code that blankets the whole range and says
1)if a cell is blank in this range and the date in CH3 is before the date this sheet was created, then colour it yellow.

2) if a cell is blank in this range and the date in CH3 is the same as the date this sheet was created, then colour it red.

then for individual column ranges within the sheet i do things so that I add a third rule.
so for instance
a cell in ck3 is red or yellow if blank, but if it contains the "wrong" text it goes pink.
therefore it has
1)if a cell is blank in this range and the date in CH3 is before the date this sheet was created, then colour it yellow.

2) if a cell is blank in this range and the date in CH3 is the same as the date this sheet was created, then colour it red.

3) if a cell IS NOT BLANK and DOES NOT CONTAIN "Brian" then colour it PINK

column b is the unique identifier for a record. the section of "kiddy code" strips out all formats and then says "look across this record, is there a blank in the data range? YES = RED"
then
"has it been more than a year since we looked at this record? YES = RED"

I'm not getting weird results in any cells other than B, where I'm trying to modify Q1 to say "well yes there's a blank in the range but you've told me that if AS3="Method C" then AT3 (to the right) can be blank so we can ignore that".

This covers what I want it to do. I export my data from a database, plonk it into excel. Apply the colour codes, then split it into separate sheets for a contributing group. Who receive an email showing them where the errors are. Because, otherwise, I'd be sat on my arse "contributing" more here.

You'll also spot that I recorded a Macro of me applying a conditional format. I then strung these together to make an ordered event of what I want to happen.

And that's why it looks like "my hovercraft is full of eels".

Then a batch-type process will do you fine and you need to avoid conditional format (a magic spreadsheet feature) and stay comfortable in the procedural world of VB. Delete all the Selection.FormatConditions stuff and set Selection.Interior.ColorIndex directly like matty does. Your logical expressions need to be in VB language too, and they need be steering you down the branches of VB If Then Else expressions, again like matty's code.

You can definitely do all that using VBA. Have a play around with the code I dropped here earlier.

This is the plan for this month. To write it properly.
Plan:
1) DL data
2) Apply colour code
3) Split into separate user group sheets
4) Save each user group sheet to a user group workbook on file
5) Select this month's user group sheet, attach to form email, send. (this bit I'm not so sure about as I'll have to check each user group sheet before sending it, but you can see where it's going).

Tiswas: I would but I keep leaning towards Access.

Fuck Access. Fuck Access in its unstable arse.

i have a 70s sun racer converted to fixed gear, i currently have cotter pin cranks
im looking to put new cranks in it with a square taper bb
anybody know which one will fit?
i was looking at this one but i dont know if it has a british thread
http://www.chainreactioncycles.com/Models.aspx?ModelID=22376

I'd take hacking about in Excel over Access any day of the week.

Access will run over your dog and steal your wife.

Ah, I should say. We have our database already. Someone is talking of bringing it from Lotus Notes to Oracle.

In the meantime the only thing I can do, to ensure people update their records is export the data from the User Interface (I can't use Lotus Notes as I then get to see Identifiable Data (unethical)).
And the developers have said "you can do all the functionality you need in Excel" and then offer no support.

(I don't mind, I'm learning (not leaning) and I like a challenge)

This is my first hack, and it works. Well it did. Until I tried to make the data entry people's job that bit easier and highlighted records that needed immediate attention.

I'll set myself the chore/task of doing this in VBA. Without recording Macros.

I have a question about where a question should go. I want to ask a question, or more probably many questions about setting up a blog or website.

i would like general advice about how things work and specific contacts and links to people who can provide the services i'll need to make it work, what i should expect to pay etc etc. I should also make it clear that i am a bit of a luddite so this may not be as straightforward as it sounds.

The idea that i have requires a basic blog format from which i can post links to embedded audio files. so presumably i'm going to need:

a - a blog
b - someone to help me make it look nice/work
c - somewhere to host (is that even the right word?) the audio files/content?

I don't know if i'm even asking the right questions. Basically it's a blog that has to have the ability to have reasonable quality sound files on it available to stream (preferably with an embedded player) and download. I need the easiest and most idiot-proof way of acheiving this. Please don't say "myspace".

Fuckinell... anyway, so my question is: "Where is the best place to ask the above and othe questions".

If it's here i'll gratefully take advice here. Or, as i suspect, if it's going to take a fair bit of explaining to this idiot, perhaps it should have it's own thread squirelled away somewhere else. Perhaps not even on LFGSS.

Thanks.

@dooks - blogger or wordpress + soundcloud.com :: free

@damo - go for it! I recommend using "option explicit" to force you to declare your variables up front - should make things easier in the long run

Dooks: What mattty said. Soundcloud will let you host individual songs, mixcloud mixes (and you don't "have" to tag each song (like you do in Soundcloud) when you do a mix.

Embed your soundcloud/mixcloud file on your blog.

cool. okay thanks chaps. i was reading the soundcloud thread yesterday (content blocked at work) wondering what this magical thing everyone was talking about was. i'll give it a go tonight then.

[ame="http://www.dailymotion.com/video/x9eacz_dani-andrada-in-alihulk-9b_sport"]Dani Andrada in Ali-hulk 9b - Vìdeo Dailymotion@@AMEPARAM@@http://www.dailymotion.com/swf/video@@AMEPARAM@@video[/ame] http://dai.ly/cmNa2a
What´s the song playing in the background between 00:15 and 00:37? The clip is from a movie called Dosage V and sadly the song is not in the credits. Anyone recognise it?

why does everything involving me and computers have to be a huge acheing pair of fucking balls? signed up for blogger with a nice clean new gmail account all good to go but every time (four times in a row isn't a coincidence i'm guessing) the moment i open the live blog page explorer crashes and shuts down. ffffff!!!!

i think i might need to install some updates. i've got them off at the moment because last time i tried installing updates on this shonky old piece of shit laptop it "configured updates" for over 36 hours. and was no less shit when it eventually decided to start working again.

sorry, this isn't a question. it's a rant.

explorer

here's the problem.

use google chrome, it's a better browser