Is it possible to run an if function, with the logical test being the colour of text in a box?

Definitely possible with VBA - don't think it's possible without that. You'd probably want to use conditional formatting?

For VBA:

Sub colortest()
If Range("c3").Interior.ColorIndex = "1" Then
Range("a1").Value = "yes"
Else
Range("a1").Value = "no"
End If

End Sub


Here's a link for a color index. ColorIndex "1" is black.


If you're using excel formats....

Sub colortest()

If Range("c3").Style = "Good" Then
Range("a1").Value = "yes"
Else
Range("a1").Value = "no"
End If

End Sub

The styles are case sensitive ("Good" works, but not "good").

You can record macros to figure out what the different styles are called, though they're probably just their names.

To expand on this, if using a macro isn't your thing, you can actually make this a custom function using VBA.
Technically, you're still using VBA, but you don't need to use a macro to get the benefits


If it's column C that you're testing, you'd put +if(colortest1(c1) = "whatever you're testing for", "yes","no")

To make this work, go to the VBA editor (alt+f11), then click on the insert menu, and select insert module. Then paste the below:

Function colortest1(range)
colortest1 = range.Style
End Function

If you're testing for color rather than an established excel style it's:

Function colortest1(range)
colortest1 = range.interior.ColorIndex
End Function

3 - I used vba for dummies just doing it is the best practice, start with simple sub routines, learning the most commonly-used objects and properties in the excel library and about data types etc.

Having a small task/function that you need automating is useful as it helps keep your attention and makes the work relevant. I hear so many people in my office saying "oh I want to learn vba"... It's not too hard, just do it!

Once you start getting proficient you can start doing some clever stuff (and impress your boss in the meantime ) i.e. "you know that report whereby we copy data from database x, then format, enter some formulas etc. then send to team y, well I've automated the whole process to run at the click of a button"

Hi,


Dim rgMyRange as Excel.Range
Dim varrMyArray() as Variant

Set rgMyRange = ThisWorkbook.Worksheets(1).Range("A1:C9")
varrMyArray = wsRange


Looping would mean you need to evaluate each cell, with a large set this would take ages. The above will do it all in one hit.

Thanks, but wsRange should be rgMyRange or what does wsRange stand for?

Ha you are right

wow thanks mate

Ok, have a sheet that I want to do conditional formatting on. Size of table is not fixed, and will likely grow

I want to change the color of a Row based on the text value of cell G in that row

So
if G2 = Clear, Fill A2:J2 w/ Green
if G2 = Problem, Fill A2:J2 w/ Red
if G2 = Complete, Fill A2:J2 w/ Yellow

Something like that. Values of G cells are fixed from short list in column R (hidden)

I haven't done any VBA for a long time. Do I have to use VBA to fill cells on G update? How do I handle every row like this?

Any other way to do this?

I have a timesheet that I would like to be able to calculate my hours based on in time, lunch and out time. It all works but I'd like the in times and out times to not display the AM/PM qualifier. I can't seem get that to work.

Basically is there some way for a cell to be a time that knows AM/PM, but doesn't show it unless you're editing.

Did it two different ways

VBA OnChange for the sheet

I didn't like that it cleared the Undo stack every time that column was changed, so I did it with conditional formatting instead using 7 rules, then format painting them across the rest of the table. I'm not sure if that's the best way to do the conditional formatting

this is very stupid i am sorry but here we go:

I have certain number, at the end i want to know how many times i wrote certain thing, number or letter what ever, just a count of how many times i wrote something: I.E.

X---A-------B--------C---------D-------------E
1 Nm1----Nm2------Nm3------Nm4---------I have
2 85pts---95pts------88pts------89 pts-------4


How to make E2? I know it is stupid i just want it to count how many times i wrote numbers in the left. bleh i am dumb

=COUNTA(A2: D2)

You can use COUNT if you're dealing with numbers (but since this is '85pts' it's a string)

overall how can excel be improved?

user interface issues?

functionality?

learning curve?

Bookmarking this thread to read tomorrow at work. I use Excel a ton at work, so for once me browsing 2+2 might actually help my job performance.

Do you know a website where I can get some good training on excel? (for free). A lot of jobs now a days require you know excel and it would be good to get to know the basics etc etc.

I use excel a little...very little and don't know jack about formulas.

I need to formula to hellp settle a small bet.

For example; I want to compare the time it would take to travel 2 miles at 60mph vs. 2 miles at 90 mph.

Then also be able to enter time and speed to determine how many miles would be covered.

I hope this makes enough sense to create a formula(s).
Thanks!

time = distance / velocity
distance = velocity * time

For the first formula I'd put distance in A1, Velocity in B1, and in C1 put =A1/B1

For the second formula I'd put velocity in A2, Time in B2, and in C2 put =A2*B2

Voila. Play with the numbers in A and B columns to your hearts content.

(I wasn't going to answer since this seems like a homework question - but given that you registered 8 years ago I'm going to assume you're not in Grade 9 science.)

Edit: This shouldn't need to be said but make sure all units match. So if you put velocity in as 90mph, use hours (or fractions of hours) for time.

I should go back to high school, but that was 50 years ago in case you were wondering. The problem I am having is that I need the answer to read out in hours, minutes and seconds. How do I format the cell in C1?
Thanks for your help! ! !

I don't know much about formatting - but you could always fake it with some simple formulas.

So lets say your units are miles and hours - do what I put in above. That'll give you column C in hours. You can make column D your hours column by saying something like =floor(C1) (not sure if this the exact formula in excel and I don't have excel here at home to try it with). Then for your minute column (E) you can do something like =(C1-D1)*60. For the seconds column (F) do something like =(C1 - D1 - (E1/60))*60.

Not super nice but functional.

There is a time format.

I am lost as what to put in C1. =floor(C1) give me an error message.

Sorry I forgot a parameter. D1 should be: =Floor(C1, 1)

grunching here...

I'm trying to use Solver to generate a solution for an equation I have, but I'm getting an error that I've never seen before: "Error in model. Please verify that all cells and constraints are valid."


google tells me:

Error in model. Please verify that all cells and constraints are valid (13).

This message means that the internal “model” (information about the variable cells, objective, constraints, Solver options, etc.) is not in a valid form. An “empty” or incomplete Solver model, or a corrupted Excel workbook can cause this message to appear. You might also receive this message if you are using the wrong version of the Solver files, or if you’ve modified the values of certain hidden defined names used by Solver. To guard against this last possibility, you should avoid using any defined names beginning with “solver” in your own application.


I have no idea what this means. pretty sure nothing is my target cells is out of the ordinary. is there a better alternative to solver?


EDIT: Goal Seek gives me a solution. I mean at this point I'm varying only one cell with one target cell, but at some point I will likely need a more complex solution, where I vary a cell with multiple constraints needed, so I still need something more robust than Goal Seek.

thanks to accobra_kid who helped me figure it out: Solver doesn't seem to like merged cells since it's a third-party add-on, which my sheet had a ton of. Removing the merged cells out of the formula cells used in my solver and it's working nicely now.

Merged cells: the bane of Solver-using Excel users everywhere!