This is a lottery challenge that I am facing at work. 6 numbers are chosen from 45 on a weekly basis. Using about 20 years worth of information, I have code that provides a dynamic frequency list in descending order.
I'd like to now have the user pick a number from the above list and see what other numbers have come up with these frequencies. I guess ultimately I'd like 2 or 3 "favorites" with the corresponding frequencies. Trouble is I've been going around in circles with nothing working
was looking if you could change a row of cells background colour if certain word is inputted elsewhere. in b2:f2 is data- was looking if, could these cells background colour change when yes is entered in G2? does not matter which is used, VBA or con formatting just cant work it out.
ie. This value M4 needs to change based on the week.
Range("M4").Select
If it's week 1, then it can be say M4, but week two will need data to go to N4.
I have found out how to get the week number from the user. ie.
' Select Week Number Dim NumSheets As Integer Dim Prompt As String Dim Caption As String Dim DefValue As Integer
Prompt = "What week do you want to get data for?" Caption = "Week Number" DefValue = 1 NumSheets = Val(InputBox(Prompt, Caption, DefValue)) If NumSheets > 52 Then MsgBox "Week Number too high" If NumSheets < 1 Then MsgBox "Week Number too low"
I tried then linking this value saved as NumSheets by:
Dim Rng As Range
If NumSheets = 1 Then Rng = Range("D3") If NumSheets = 2 Then Rng = Range("E3")
I have a spreadsheet set up for recording a business's reply times and what i want to happen is if say for example the cell M4 was greater than the cell K4 i want that whole row to be highlighted red.
I could not find an easy solution to the find/remove duplicates in a row from left to right.
I have an other option: sort all data in one row, then it will show me, if there are any cells with the same word. But .. here is my first problem:
First row is A1: AS1 with data. A2: BB2 with data A3: BD3 with data A4: BX4 with data and so on.
When I start at A1 and press Shift/CTRL/Arrow right, it will hilite the row until BX1 and if I use the sort command, it will place all empty cells in front of the data.
What I need is hilite only up to the last column in that row.
So I could slowly work myself downward. Or prehaps there is a formula?
I could insert an empty space and write the formula in A1 and copy it down to A1644.
So it's pretty basic. If we simply remember the first ColumnA and ColumnB data, go to the next row, if ColumnA is the same, then ColumnB should be the same. If it's not, then fill with red, go to the next row, repeat. if ColumnA changes, remember the new pair, go to the next row and compare again.
I've thought about this from a scripting perspective, and imagine that something like this would do the job:
Code: sub FindBadTermID () Dim row As Integer Dim dataA as string, dataB as string Set row = 2
X-Axis = Week1:Week13 Y-Axis =Revenue for each week.
I have a line graph. If "Week9:Week13" have no value, my graphs line drops to 0. How do I make my line graph show no value when no value is inputed for "Week9:Week13"? I think I need to incorporate the =NA() in the original formula but I'm not sure how to do this.
I have a spreadsheet where I have a column of dates and I want conditional formatting to highlight the cell red if the date is less than or equal to today but if the cell is blank to do nothing.
At the moment I have the following formatting applied using the "format only cells that contain" option I have cell value less than or equal to =TODAY() except that obviously highlights every cell red that doesn't contain a date. Is there another conditional format I can apply in addition to this that will not highlight the blank cells ?
I have a userform which has a textbox for employee id. If an employee enters less than 5 digits, it should prompt the user via msg box that he needs to enter 5 digits for employee id. However my coding doesnt work...can someone help me correct it?
I have a cell containing data validation list and what i want to do is that when i choose the 1st value from this list .. a number (for examole 1) appears in another blank cell .. and if i choose the 2nd value from the list .. another number (let's say 2) appears in the blank cell.
So how to use the if statement in a cell that contains a data validation ?!!
What i would like to happen is after inputing a number into say column B have that number update a formula in cell D2.... so for example... lets say i have
is there a way to make it so when i enter a new number in B4 it will automatically update the formula to display the value for (B4-B3)/B3 and then continue on to repeat this process when i enter values into B5, B6, B7 ect?
I want to enter a unique ID into an input box which will tell which row that id is available and then it will again ask me which column the cell needs to be highlighted. Once i enter the data, it will then ask me what is the change in data (again via input box). i can then enter the change data and then it stops.
I'm trying to set up an if statement that will recognize that if a cell is FHR it will do something...but if it's PHR it will do something else. I think I found the place where I keep getting an error but I'm not sure how to go about fixing the issue.
the task for the code below is to search through rows in array and check if there's no "text" in it like user inputed in box. But it always shows that there's no such email,i can't figure out the mistake, Maybe the problem is in the type the InputBox returns? But i've tried entering type := 2 (text),dunno what else i can do...I've also tried using p = InputBox("Please,eneter email!").
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
I'm after a formula (or similar) that if cell H1 has no data in then cells A1:G1 must be highlighted in a colour, considering A1:G1 has data in. And then when data is input into cell H1 then the highlight disappears. is this possible?
I need a code please that will look in Column C in every workbook (excluding the sheets “Blank”, “Orders”, “Summary” and “Archive”) and then it will highlight duplicates in Red and Bold.
I found the following code but I need it so it looks on every page excluding the ones mentioned above.
This is a conditional formatting problem I have not been able to resolve:
I have a range, say A1:N30
Each cell contains a text/number combination.
I would like to highlight each cell on a specific row if the vale exists within the row above - the issue is that the cell values are not in the same columns.
I have attached a sample workbook with the desired output.
I have a spreadsheet where if Cell A2 says "Deposit", Rows A2-E2 would be formatted with a Blue background.
I've tried conditional formatting, but I can't built it into a Macro (I can't make it run), and it only highlights the cell that has that value. I also tried having the spreadsheet filter down to only the values I want highlighted, then highlighting all cells and un-filtering the column- this didn't work either.
It would basically say :
If (any cell in Row A) has text = "Deposit" (it needs to be exact, it can't be "containing"), set cell with the word "Deposit" and 4 cells to the right as Blue (I'm not picky about the color).
If I wanted to highlight a cell if any cell in the range to the right was greater than zero, what formula would i use. I have tried =IF(L1:AD1>0,1) with the result returning for only the cells in column L. Row 3 has no value in column L but a value in column N with no result to highlight the cell.
see attached sheet. Column A has File Name. Need to highlight the duplicate data as you can see there is 2 duplicate data which i have manually highted ( C19 is duplicate of C12, C83 is duplicate of C84).